How to search multiple worksheets and put results in a seperate worksheet.

See attached for formula based approach.

In each Parts sheet I added a helper column to count matches to Part number in the Search sheet.

Formula in Z2 of each sheet is

=IF(C2=Search!$A$2,MAX($Z$1:Z1)+1,"")

copied down

In each Part sheet after the first, at the top of the Z column is a formula to find the Max number from the previous page to add number of matches in current page on to...

So in Z1 of PART2 and PART3, there is formula:

=MAX(PARTS1!Z:Z) and =MAX(PARTS2!Z:Z), respectively.

Then in the Search page there is a count formula in D1 that shows the maximum number on the last Part3 page...

=MAX(PARTS3!Z:Z)

Then, to extract the data, we have formula in A6 of Search page:

=IF(ROWS($A$6:$A6)>$D$1,"",IF(ISNUMBER(MATCH(ROWS($A$6:$A6),PARTS1!$Z:$Z,0)),INDEX(PARTS1!B:B,MATCH(ROWS($A$6:$A6),PARTS1!$Z:$Z,0)),IF(ISNUMBER(MATCH(ROWS($A$6:$A6),PARTS2!$Z:$Z,0)),INDEX(PARTS2!B:B,MATCH(ROWS($A$6:$A6),PARTS2!$Z:$Z,0)),IF(ISNUMBER(MATCH(ROWS($A$6:$A6),PARTS3!$Z:$Z,0)),INDEX(PARTS3!B:B,MATCH(ROWS($A$6:$A6),PARTS3!$Z:$Z,0)),""))))

copied down as far as you need and across all required columns.