merge all workbooks to one workbook

I have 50 templates of the same format, which has multiple tabs in it, all the 50 templates have to be consolidated by summing each cell from all the templates and give a summary file.

for example: I will receive PnL templates from 30 different regions which needs to be consolidated by adding all the cells (except for the cells where i have formulas) and create a summary tab

i am attaching a sample it have to consolidate all yellow cells. the path for each file will be different, a message box will be popped up which will ask for the path of the files

thanks a lot in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:

The file is missing...

NirvanalearnerAuthor Commented:
sorry here is the attachment
Ejgil HedegaardCommented:
Try attached file, use the update button.
I have set "Sheet1 (6)" as the summary sheet.
The function first clear the cells where the cell colour is yellow, and the cell does not have a formula (row 8 has formulas in yellow cells).
Then ask for a folder to use.
Loop all xls files in the folder and get data from sheet "Sheet1 (6)" for the same cells.
The sheet name must exist in the data file.
If cell value is numerical and not equal to 0, the value is inserted in the cell on the summary sheet "Sheet1 (6)", or added if the cell has a value.
If no non zero value is found for a cell, the cell remains empty.
When there are no more (xls) files in the folder, the function ask for another folder.
The update stops when no folder is selected.

The function makes links to the files, which is much faster than opening the files to get the data.
It is only made to get data from one sheet in the data files, but can easily be expanded to more sheets, as long as the sheet names are known.

Your file was Excel 2003 (xls), so I kept that format, but the links method will not work for later versions of data files, Excel 2007 on.
If you have Excel 2007 data files, the function must be run in Excel 2007 (or later), and then the search command (Dir) for the files, changed to accept those files.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NirvanalearnerAuthor Commented:
Hi Ejgil Hedegaard.. the solution is great however just need some tweaks.. sorry for providing a sample file of 2003 this have to done in versions of 2007 i have updted dir to xlsx.. and

also can you provide a solution to update all sheets. the sheets names will be always same

the regional finance managers will update this file. how can i make sure this is not updated twice and build a control..

thanks a lot again for your time. really really appreciate it
Ejgil HedegaardCommented:
Since the files are not opened, the program can not find the sheets to get data from.
The sheet names are part of the created links, and must be known, so please provide a list of the names.
This is a disadvantage of the method, where the advantage is the increased speed.

Since the cells in the summary sheet is cleared when update starts, the second update will give the same result, unless the update is broken before finish, = not all folders selected.
A list of folders to use could prevent that.
Or the files stored in a series of folders in a top folder (preferably one level), so only the top folder is selected.
When the top folder is selected, instead of the path defined in the code, it is more flexible.
If a new folder is added it is automatically used also, without having to remember to add it to the list.

Please upload the summary file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NirvanalearnerAuthor Commented:
super solution. thank you very much. yes there are some changes that have to be building a control, where an user migh click same file twice so it might double entry.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.