Forecast KIT splitting based on BOM list

[SPet]

Hi All,

I'm not familiar with Excel VBA macros...

I have a forecast sheet (Sheet1) and a BOM list other sheet (Sheet2).
The forecast sheet contains the item codes in the first (A) column and the forecasted quantity by week per column.
The BOM list sheet contains the KIT item code in the first (A) column, KIT description in column B , Component code in column C and Component description in column D.
The forecast item codes contains simple products and kits.
The KITs could be included one to multiple components.
I would like to use macro for the following process:
- search the item code from the beginning of the forecast sheet on the BOM sheet if finds a match (means this item is a KIT) insert a row or insert row below the item code on sheet1 and insert the component code(s) from the sheet2 column C to this(these) rows and copy the quantity of the KIT (the KIT row is above these new rows) to this(these) rows.
This should run row by row until find an empty cell.

I tried several sample codes from Excel Forum for example this:

Please Login or Register  to view this content.

I 've modified the above code but it is find and write on the active sheet only not write on the other.
And I can insert row only above of the cell but I need it(them) below of the cell.

I'm using Excel 2007.
I have attached the sample.
Forecast_BOM.xlsx

Do you have any ideas?

Thanks in advance.

Peter

[jindon]

1) You need to edit your post and wrap the code with the code tag.
It is a MUST rule here
e.g
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

2) How do you want the result? If you upload the file with your desired result from the data provided, it will be clearer.

[SPet]

1. Sorry.
Modified the post based on the rule.
2. The attached file sheet1 47-49 rows show a sample. If the KIT code (422203923314) find in the KIT list (sheet2) then the components code (422200252183, 422200272624) copied(inserted) below the kit code(sheet1) and the kit forecasted quantity copied to the inserted components rows. I inserted the two components code above the KIT code and copied the quantity manually.
Forecast_BOM_1.xlsx

[jindon]

Not sure if this is what you wanted.

Please Login or Register  to view this content.

[SPet]

It is working properly.
This is what I need exactly.
Thx.

[jindon]

OK and thanks for the edit (code tag)

Now please mark this thread as "Solved".