Consolidate data from rows in many sheets to summary sheet?

[Dynelor]

Hi
I have a Workbook containing about 150-200 sheets.
Each sheet is a product specification, and the information begins in A5 to E5. The number of rows is different for each sheet. Each sheet is named with the letters ‘Part-‘ followed by some number, like 123456. For example Part-123456.

In column D in each sheet, for some of the rows in D5 and down, this Part-number is given. That means that for this row, there is a other sheet with that ‘Part-number’ containing the underlaying product specification. This means that from the “Top-sheet”, there might be 30 or more underlaying levels (sheets). In Column E, the last columns have only two values, “Top”, or “Bottom”.

If “Top” is given for a row, then I would like that row to be copied as is and paste into a summary-sheet, beginning in A1-E5. If instead “Bottom” is given in Column E, then the program should go to the correct ‘Part-xxxxxx’ sheet stated in Column D, for example ‘Part-2111111’ and as before start checking the rows in the exact same way, all the way until every row, from the “Top” sheet have been copied to the last underlaying sheets.

I really need help figuring this out and help with the code.
It would really make my day if this is possible to do, because I will need this for a lot of different Excel workbooks.

[rylo]

Hi

Why can't you just cycle through each sheet and copy over those rows that have "Top" in column E? Are you trying to build some sort of hierarchy of sub products based on the first entry? If so, then what are you going to do with the multiple copies of rows as you progress through the sheets?

If you only bring back the "Top" rows, is there enough data in columns A-D to do a sort and put them in a required order?

Perhaps build a small representative example workbook, and explain what rows should be brought back and why.


rylo

[Dynelor]

Hi Rylo

Thanks for your replay

I understand what you mean, and I have not though of it like that, but I think you are right. Besides, writing the code so I can get a summary list based by hierarchy of sub products depending of the first entry is beyond my VBA-knowledge.

So, I think the idea you gave me is better. Just to copy the rows containing the "Top" in column D. That way I get a summary of the total product, that is the important thing anyway.

I have tried to write some code to do this, but I can not get it to work, its something wrong and it does not copy anything.

Here is the code:

Please Login or Register  to view this content.

[rylo]

Hi

See how this goes. It creates a new sheet at the start of the workbook, names it Summary, and checks all the rest of the sheets for relevant data.

Please Login or Register  to view this content.

rylo

[Dynelor]

Hi Rylo

Your code works great!
Thank you

I am learning a lot from study the code you wrote.

I am now trying to get some more function but I dont really understand how to do it.

What I want to do is to copy some more information for each line copied into the summary-sheet. I want the program to take the value in B2 from the "copy-from sheet" and then paste the value into the summary-sheet in column G. Also some other cells I want to be copied, I have a list below.

From the sheet
beeing copied from -----> Summarysheet

B2 ------------------------> G
B3 ------------------------> H
G1 ------------------------> I
B1 ------------------------> J


I figured it is here I should put the code, but I dont understand how.

Please Login or Register  to view this content.

[Dynelor]

I solved it!

Thank you again for your help Rylo!

[rylo]

Hi

Great. Good to see that you have come away with something from the code.

rylo