Consolidate data from multiple workbooks

Hi All,

I have 52 workbooks with approximtely 20 tabs on each, each workbook being identical. One tab has a scorecard on that I would like to have a formula or VBA to sum each workbook on to a summary sheet.

So for example Sum all "A1" from all 52 workbooks.

I have tried to use the function consolidate but this does not update automatically.

There are approximately 50 cells in the scorecard & 7 scorecards on each worksheet so I do not want to manually =sum('[Week 1.xlsx]Sheet1'!$A$1+[Week 2.xlsx]Sheet1'!$A$1.......and so on, & obviously manually using consolidate would e a pain also.

The formula =Sum(Sheet1:Sheet52!A1) would not work either as this only works for sheets in the same workbook not different workbooks.

Can anyone offer up a suggestions, than you in advance for you time.

Cheers
John

Hi John,

Below is code that will loop through all of the excel files in a directory and sum a specific cell across all worksheets. I don't know if this is exactly what you need, but I think it has the fundamental parts you are looking for. Hopefully this helps out.

Please Login or Register  to view this content.

Thanks,

Daniel

Hi Daniel,

Thank you for your time & help.

I have tried the below which returns a zero in cell D6, each cell D6 in files "Week1", "Week 2", etc have a value of 10. Therefore the result should return 130.

Can you spot the error?

Thanks
John

Please Login or Register  to view this content.

Hi John,

The line where you listed files all the ".xlsx" file together in a list can only call one file at a time. The way it is written above it would be looking for a file called "F:\Test\Week 1.xlsxWeek 2.xlsxWeek 3.xlsx.... etc". You are looking to call files "Week 1.xlsx" to "Week 13.xlsx" in order, then a loop can be put in to call each file individually and sum up cells D6. I revised the code and it is shown below. The "Do - Loop" no longer is relevant if you specifically call out each file rather than all files in a directory.

Please Login or Register  to view this content.

Hope that helps,

Daniel