I think I'd create a new (temporary) worksheet that contained the part codes in
column A and the worksheet name for each code in column B.

If you want to try a macro, this worked ok for me:

Option Explicit
Sub testme01()

   Dim wks As Worksheet
   Dim RptWks As Worksheet
   Dim SummWks As Worksheet
   Dim DestCell As Range
   Dim RngToCopy As Range
   
   Set SummWks = Worksheets("Summary") '<-- change this
   Set RptWks = Worksheets.Add
   With RptWks
       .Range("a1").Resize(1, 2).Value = Array("Part#", "Category")
       .Range("b:b").NumberFormat = "@" 'text
   End With
   
   For Each wks In ActiveWorkbook.Worksheets
       Select Case wks.Name
           Case Is = SummWks.Name, RptWks.Name
               'do nothing to these sheets
           Case Else
               With RptWks
                   Set DestCell _
                       = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
               End With
               With wks
                   'This copies the data in column A in each worksheet
                   'change this (twice) to the column that you use
                   Set RngToCopy _
                       = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
               End With
               RngToCopy.Copy _
                   Destination:=DestCell
               DestCell.Offset(0, 1).Resize(RngToCopy.Rows.Count, 1).Value _
                   = wks.Name
       End Select
   Next wks
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

After you have this extra worksheet created, you could use =vlookup() with just
one lookup range to return the category for each part number.

1 - Are the worksheet names the same as what you call "codes"?
2 - How does someone determine the category the code is in?   It's not
intuitively obvious.