Consolidate Data from Multiple Worksheets on One Workbook into a WS on a Different WB

[agpuh]

I am new to trying to create macros, so while I have been able to figure out one or two of the components I need I am unable to creat one cohesive macro. Below is a representative description and sample information of what I need help accomplishing.

In Workbook A (Sample Lot Breakdown), new sheets are constantly being added based on the current production lot. The data of interest on the worksheet are not in consecutive cells, but are in the same cells on each worksheet.

In Workbook B (Sample Component Analysis), the data needs to be compiled so that the data from a lot/single worksheet is a single column and the column shifts one right for each lot/worksheet added.

There is no upper limit for total number of worksheets that may be available in Workbook A, so Workbook B needs to update (using a button) to include any new information. Some components in the analysis need to counted twice (you can see which for this sample data in the formulas given).

Does anyone have an idea on how to accomplish this?

[agpuh]

What I have so far can copy the information and iterate through the worksheets, but I haven't been able to limit the selection to the relevant information across different stages, order them, or shift the column for each lot.

Please Login or Register  to view this content.

[tanasedn]

In 'Sample Lot Breakdown' workbook you have merged cells in every sheet.
Can you, please upload a sample without merged cells? Or to explain what that cells value means.
In workbook no.2, you have a formula but I cannot see because it reference a path that I don't have. So, can you add a single quote before equal sign in formula cell?bThis will help me to understand what you want with that formula

[agpuh]

Updated the original post with the changes requested. Hope this helps.

In the excel document, the reason some cells are merged is because at that point of assembly a particular scrap code means all the components are affected. It is a feature that production manager uses that I wanted to replicate for the sample.