1 Attachment(s)
VBA - Search For Value Across Multiple Worksheets
Hi VBA Experts,
I wrote code that I except it to find a value that exists in multiple tabs and return a specific cell where that match exists. Here is the code, and I have attached a small version of the document with the code. In this example I expected to have the program return the worksheet name and the PO's found in each of those tabs. However, It is only picking up the first tab, but yet there is data in both tabs for the instance of PO 235204.
Sub Multiple_Sheet_Count_Match()
Application.ScreenUpdating = True
totalsheets = Worksheets.Count
POs = Worksheets("End Result").Cells(2, 4).Value
For i = 1 To totalsheets
If Worksheets(i).Name <> "End Result" Then
lastrow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To lastrow
If Worksheets(i).Cells(j, i).Value = POs Then
Worksheets("End Result").Activate
lastrow = Worksheets("End Result").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("End Result").Cells(lastrow + 1, 1).Value = Worksheets(i).Name
Worksheets("End Result").Cells(lastrow + 1, 2).Value = Worksheets(i).Cells(j, 1).Value
End If
Next
End If
Next
Application.ScreenUpdating = False
End Sub
Any ideas would be greatly appreciated.
Thanks.
Steve