Hi, I have been trying to figure this out on my own by looking through the forums but I haven't been able to get my code to work the way I want to. My code below IS working but it is stopping at the first value it finds. I need it to continue searching the worksheet until it finds all matching values. The worksheet will be constantly changing so the code/search needs to be dynamic.
Here is what I'm trying to do:
- Search all visible rows in column A on worksheet "Offer Setup Tracker" for text "PROCESSING DATE:"
- If it finds "PROCESSING DATE:", copy the adjacent cell value in column B
- Paste that copied value in the next available row in column O on worksheet "2013 Totals"
I have attached my file here if it helps. I also put my desired result (a list of dates) in column O on the "2013 Totals" tab.
I have my code in the Workbook_Open slot because I want it to run every time I open the workbook.
Can anyone help? Thanks very much.
forum.ozgrid.com/index.php?attachment/51508/
Code
Dim Cell As Range
Set Cell = Sheets("Offer Setup Tracker").Range("A:A").SpecialCells(xlCellTypeVisible).Find(What:="PROCESSING DATE:", _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Dim NextRow As Long
NextRow = Sheets("2013 Totals").Range("O65536").End(xlUp).Row + 1
If Not Cell Is Nothing Then
Range(Cell.Offset(0, 1), Cell.Offset(0, 1)).Copy Destination:=Sheets("2013 Totals").Range("O" & NextRow)
End If
Display More