Combine data range from multiple workbooks into one worksheet with additional variable

[Brontosaurus]

Hello, I have searched for a solution to this requirement in the many examples which exist but can't find one that does what I need exactly and my vba skills are not good enough to adapt. I would greatly appreciate help from the excel expert community.

I'm trying to combine several BOM files into one worksheet which I can then use with a pivot table. All workbooks are located in one folder. The source workbooks are based upon the existence of several top level part no BOMs - each contains the enquiry date and top level part no in specific cells (A1 & B6) and then the BOM itself starts at row 12 in cells B12:ARx where the number of rows is a variable but the relevant rows always have a number in column B. If there isn't a number in column B from row 12 then the data isn't required. There is only one worksheet containing the BOM for a specific top level part number in each workbook.

The sequence would be;

1. Value of cell A1, B6 and data in range B12:ARx copied.
2. Data pasted into workbook containing the macro (which is located in a different folder to the source data workbooks) such that Cell values of A1 & B6 are repeated on each row pasted. Data from workbook 2 would be pasted under data from workbook 1 etc.
3. Close source workbook and open next workbook, repeat until all workbooks have been processed in the same way.

Please could somebody get me started with this or point me in the right direction?

The code below works slightly differently but may be a useful starting point;

Please Login or Register  to view this content.

Thank you,

Jess

[Brontosaurus]

Here's one example of the source data as attached.

Thank you for looking and any help appreciated.

Jess

[Brontosaurus]

Hello, is anyone willing to help with this please or suggestion of a better way to ask?

Thank you