Consolidating Workbooks in a folder into one Workbook


New Member
May 24, 2018
Hello all,

I am brand new to the forum and have done a search for the answer I am looking for but none of the current posts do what I want and I have got in a bit of a muddle now, so I decided to ask outright - I hope someone with a brain much bigger than my own can help me solve my problem.


I have created a folder in which are several workbooks.
Each workbook is the same format, same calculations and layout, but with different data from different business areas.
Each workbook has several sheets which are called different things depending on which workbook they reside in, and each workbook has a consolidation sheet that sums up the other sheets using the formula =SUM('0:1'!CELLREF) so that it sums up the sheets placed between 2 sheets labelled 0 and 1 with no data in them, and allows us to add and remove sheets for summary in that workbook.

What I want to do is create another workbook (Summary) which imports all the sheets less the consolidation sheets from the other workbooks and has its own consolidation sheets in which it carries the same formula to allow insertion and removal of individual data sheets as before.

On top of this, it must do this for whatever workbooks are present in the folder, so we can add or remove entire workbooks easily.

I hope that all makes sense, and I would like to thank you all for having a look and hopefully being able to assist.



Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics