Calculate committed inventory total based on BOMs and a schedule

Hi there,

Looking for some help calculating committed inventory based on a schedule and number of BOMs (bill of materials). So basically I have a workbook with the first worksheet being a schedule of widgets to build with part numbers, a second sheet with all the parts used in building the different widgets, and then a number of BOMs (each on separate sheet with unique part numbers) with parts required for each different widget. Each widget has a unique list of parts and quantities used but some of the parts are used across multiple widgets. A widget may show up more than once in the schedule or not at all.

What I'm trying to do is go through each widget in the schedule then add the parts required to build each into the 2nd sheet so I can have a running total of committed inventory. I've attached a sample workbook if my description is tough to follow.

Any help would be greatly appreciated. Cheers!

Is this how you wanted?

Please Login or Register  to view this content.

Please see attachment. I've amended your sheet so that all widgets are on a single sheet, this way you can add more widgets without ending up with hundred of sheets.

Please Login or Register  to view this content.

Thank you both for your help. Both solutions posted worked but I'm looking to keep the BOMs on separate sheets as that is how the data is currently stored. Also, jindon's solution gives me an out of range message when I add or delete sheets. Also, I would like to show all the parts in the "All Parts" worksheet whether they are 0 or not as I will pulling that data into another workbook.

In addition to this, the schedule data may not be stored in such a perfect manner. The data may start in a lower row and column. How would I modify the macro if that were the case? I'm a bit new to vba but I have a background in java/c++.

Thanks again!

Try the attached