VBA - Search For Value Across Multiple Worksheets

[Steve Belsch]

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