Good day, Gurus.
I have a macro that copies data from the active workbook to another workbook. If the data is already in the other workbook, however, I want to give a message, "This PO Number is Already in the Tracking Log", and then exit.
The value I want to search will always be found in cell M2 of the active workbook, and I really only need to search Column M of the other workbook to see if the value is found.
Here is what I have so far. The area with the extra line breaks is where I need to add the code to search and either give the message and exit or continue to run the macro:
Sub Move_PO_to_Log()
Dim POnumber
Dim wbTemp As Workbook
Set POnumber = Range("M2")
Set wbTemp = ActiveWorkbook
ThisWorkbook.Activate
'The code below will search the whole sheet for the POnumber,
'and then give an error if it doesn't find it. I really need
'it to only search Column M, and give a message and exit if
'it finds the number, or continue if it doesn't.
Cells.Find(What:=POnumber, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'The code belwo will work fine to copy the data needed
'if the POnumber is not found in the search.
wbTemp.Activate
Range("A2:T" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Copy
ThisWorkbook.Activate
Range("A1048576").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("U1048576").End(xlUp).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Range(ActiveCell, "U" & Cells(Rows.Count, "A").End(xlUp).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A3").Select
End Sub
Thanks in advance for any help you can offer.
Bookmarks