Extract Specific Data from Multiple Workbooks

I may be in over my head..

I've been learning a lot about VBA recently, but programming between multiple workbooks threw me for a Loop. Most of what is out there on this topic focuses on collecting entire workbooks or copying entire columns into a single workbook. I'm trying to extract only specific information from hundreds of different workbooks and concatenate into one spreadsheet.

Basically I have hundreds of 'bills of material' spreadsheets and need to search through all of them to find part numbers that correspond to graphics and return the value of the kit number on which the graphic was used. I have attached a small sample of the bill of material template for reference. I have a list of part numbers that relate to graphics available and need the program to open each workbook and find whether any of the part numbers listed are also on the graphics part number list. IF SO, it should find which items the graphics were used on, i.e for every cell in that row from H to JS that is greater than Zero, return the values in that column for rows 4 and 5 (kit number and description) onto my 'Summary Sheet', which could basically be a blank workbook with the graphics part number database listed in column A. Example also attached for reference.

I need the macro to go through every part number listed in every bill of materials file and compare it to the graphics database and return the proper values when a match is found. I'm not too worried about organizing the information in a pretty way, as that can come later. I just need to get it dumped into one place first (following some kind of logic)

Sorry for the long-winded intro...

Anyway, I have attempted to code this a couple of different ways without much success. Can someone please tell me what I'm doing wrong??

Please Login or Register  to view this content.

Thanks to hiker95 for the basis for this code, but I can't seem to get it to work for me..

Can someone please tell me what I'm doing wrong??

Thanks!!