I am trying to create a message box if a certain cell is equal to a certain rule and am having some difficulty.
If cell D26 = "create a special batch", I would like a box to appear that tells the user to "enter the special batch number", then click OK. Once they click OK, I would like that to go to a RECORDS tab in column Q. My current code is below:
If cell D26 = "create a special batch", I would like a box to appear that tells the user to "enter the special batch number", then click OK. Once they click OK, I would like that to go to a RECORDS tab in column Q. My current code is below:
VBA Code:
Sub SENDTOLOG_Click()
Application.ScreenUpdating = False
Dim copysheet As Worksheet
Dim pastesheet As Worksheet
Set copysheet = Worksheets("Input")
Set pastesheet = Worksheets("Records")
If Range("G4") = "" Then
MsgBox "Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX"
Range("G4").Select
ElseIf Range("G7") = "" Then
MsgBox "Please enter the LINE QUANTITY" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("M7") = "" Then
MsgBox "Please enter the QTY REJECTED" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("G14") = "" Then
MsgBox "Please enter the TICKET LOAD DATE" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("M14") = "" Then
MsgBox "Please enter your name in the REJECTED BY: BOX" & vbCrLf & " " & vbCrLf & "XXXXXXX"
Else
MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."
'Copy data from the INPUT to the RECORDS worksheet
'copy CUSTOMER #
copysheet.Range("M4").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'copy CUSTOMER NAME
copysheet.Range("G4").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues
'copy ENTRY DATE/TIME
copysheet.Range("D17").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).PasteSpecial xlPasteValues
'copy TICKET LOAD DATE
copysheet.Range("G14").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).PasteSpecial xlPasteValues
'copy LINE QTY
copysheet.Range("G7").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues
'copy QTY REJECTED
copysheet.Range("M7").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 5).PasteSpecial xlPasteValues
'copy % REJECTED
copysheet.Range("P7").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 6).PasteSpecial xlPasteValues
'copy RULE
copysheet.Range("G11").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).PasteSpecial xlPasteValues
'copy RESPONSE
copysheet.Range("D26").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).PasteSpecial xlPasteValues
'copy rejected by:
copysheet.Range("M14").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 9).PasteSpecial xlPasteValues
'copy before 2pm?
copysheet.Range("S24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 10).PasteSpecial xlPasteValues
'copy load date after today?
copysheet.Range("T24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 11).PasteSpecial xlPasteValues
'PO start w/ number?
copysheet.Range("U24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 12).PasteSpecial xlPasteValues
'copy less than 10 rejected?
copysheet.Range("V24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 13).PasteSpecial xlPasteValues
'copy 5 or less rejected?
copysheet.Range("X24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 14).PasteSpecial xlPasteValues
'copy less than % rule?
copysheet.Range("Y24").Copy
pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 15).PasteSpecial xlPasteValues
Sheets("Input").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
End Sub
Last edited by a moderator: