Consolidating data from multiple worksheets into 1 sheet

Presently, I am copying and pasting data from multiple worksheets (48 worksheets in total at this stage) into the 1 sheet, and would like a quicker way to do this.

The data from each worksheet is in the same position on each sheet: A1 to D100.

I would like the data in my summary sheet to be placed one under the other, so the first block of data in the summary sheet, for example, could be A1 to D100 (extracted from Worksheet 1), then I would like the next lot of data (extracted from Worksheet 2) to be placed on the summary sheet in A101 to D200, followed by the data extracted from Worksheet 3 to be placed on the summary sheet in A201 to D300, and so on.

 

Question Info


Last updated October 2, 2019 Views 6,519 Applies to:
Answer
Answer

Lets do in this way.

Assumed you have 48 sheets, create 2 more sheets. One with the Tab Name "Master" and other with Tab name "Report"

Now in your Master sheet in A1, put the number of rows which you want to consolidate in Report sheet.  Say in your case 100. So put 100 in A1 cell of Master sheet.

In B2 to B50 give the name of different sheets which you want to merge.

In A2, put this formula =$A$1*(ROW()-2)+1 and drag down till your data is in B column.

 

And now finally give this formula in Report sheet at cell A1

=IF(OFFSET(INDIRECT(VLOOKUP(ROW(),Master!$A$2:$B$500,2,1)&"!A1"),IF(MOD(ROW(),Master!$A$1)=0,Master!$A$1,MOD(ROW(),Master!$A$1))-1,COLUMN()-1)="","",OFFSET(INDIRECT(VLOOKUP(ROW(),Master!$A$2:$B$500,2,1)&"!A1"),IF(MOD(ROW(),Master!$A$1)=0,Master!$A$1,MOD(ROW(),Master!$A$1))-1,COLUMN()-1))

 

and drag down right and down.

Your data is consolidated. 

Cell A1 of Master sheet is dynamic which you can change as per your requirement. Like presently it is 100, which you may change to 1000 or 10 and accordingly report will be merged for all those number of rows for all the sheets given in "B" column.

 

Vijay

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.