A bit easier to put the results on a sheet somewhere so this code will put the results on the 'Search' sheet, in 4 columns starting at column J. You can alter that starting column (ResultCol) in the code if it isn't suitable.
The code lists the product code then below that each sheet name with the other data in the columns beside.
<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FindAllInstances()<br> <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, wsS <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <SPAN style="color:#00007F">Dim</SPAN> FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sProd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Dim</SPAN> NextRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ncols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> bProdStarted <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> aCols<br> <br> <SPAN style="color:#00007F">Const</SPAN> SearchCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "E"<br> <SPAN style="color:#00007F">Const</SPAN> DataCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A,B,D"<br> <SPAN style="color:#00007F">Const</SPAN> ResultCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "J"<br> <br> Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br> aCols = Split(DataCols, ",")<br> ncols = <SPAN style="color:#00007F">UBound</SPAN>(aCols) + 1<br> <SPAN style="color:#00007F">Set</SPAN> wsS = Sheets("Search")<br> <SPAN style="color:#00007F">With</SPAN> wsS<br> sProd = .Range("G1").Value<br> NextRow = .Range(ResultCol & .Rows.Count).End(xlUp).Row + 1<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br> <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Search" <SPAN style="color:#00007F">Then</SPAN><br> FirstAddress = vbNullString<br> <SPAN style="color:#00007F">With</SPAN> ws.Columns(SearchCol)<br> <SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:=sProd, LookAt:=xlWhole, _<br> MatchCase:=False, SearchFormat:=False)<br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> FirstAddress = Found.Address<br> <SPAN style="color:#00007F">Do</SPAN><br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> bProdStarted <SPAN style="color:#00007F">Then</SPAN><br> wsS.Range(ResultCol & NextRow).Value = sProd<br> bProdStarted = <SPAN style="color:#00007F">True</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> NextRow = NextRow + 1<br> <SPAN style="color:#00007F">With</SPAN> wsS.Range(ResultCol & <SPAN style="color:#00007F">Next</SPAN>Row)<br> .Value = ws.Name<br> <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ncols<br> .Offset(, i).Value = Intersect _<br> (Found.EntireRow, ws.Columns(aCols(i - 1))).Value<br> Next i<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> Found = .Find<SPAN style="color:#00007F">Next</SPAN>(After:=Found)<br> <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Found.Address = FirstAddress<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> Next ws<br> Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>