[RESOLVED] [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook

[ammu]

Dear All,

I am currently having 55 workbooks in a folder (ex. “C:\Consolidation”) and each workbook contains 4 worksheets.

Now I need to consolidate the following three sheets 1. Total Consolidation, 2. State level Consolidation and 3. District Level Consolidation from all 55 workbooks and save it in three different sheets in a new workbook in the same folder (i.e. C:\Consolidation). That new file should be names as “COUNTRY LEVEL CONSOLIDATION”.

So now macro should open one workbook at a time and copy three worksheets separately and paste them including the heading (heading should be copied only for the 1st workbook and second workbook onwards no need to copy the headings) into three worksheets in a new workbook named as “COUNTRLY LEVEL CONSOLIDATION”.

When it opens the second workbook, it should again copy three worksheets data and paste them below the data that are already copied from the previous workbook. (Ex: assume that previous workbook data copied till 500 rows in 1st sheet, 150 rows in second sheet and 90 rows in third sheet then, second workbook data should be pasted from row 501 in first sheet, 151 in second sheet and 91 in third sheet excluding headings. This is how it should copy all 55 workbooks data into three work sheets in one workbook. But the current challenge is in each work sheet, the number of rows to copy is different.

I have attached 5 sample workbooks for your reference.

Is it possible to develop a macro to do these tasks? Please help me with the macro code.

Thank you so much for your help in advance.
Amrutha