How can I combine multiple files into one summary worksheet?

[Bear]

My branch colleagues will send me a shift schedule weekly, I was tasked to combine 45 workbooks into a summary table by days of week. Each excel table from each branch has 7 spreadsheet e.g. Mon, Tue, Wed, Thu, Fri, Sat & Sun, I need to prepare a summary sheet on day of week, e.g. Monday's data is updated on the worksheet named Mon,....

I tried different methods but it still doesn't work, so I seek help from here.

I tried the following methods
1) Used consolidation method - but it only works for data only rather than text
2) found a ablebits data function - but it required licence cost
3) copied VBA code from the website - but it only works for the first worksheet of each workbook, however, my workbook covered 7 spreadsheet, Mon, Tue, Wed....

Luckily, the format of each worksheet is the same. I attached a sample files here and hope any expert can offer your insight.

Thank you

 

[Belleke]

Maybe this is helpfull.
https://www.rondebruin.nl/win/addins/rdbmerge.htm

 

[Bear]

Maybe this is helpfull.
https://www.rondebruin.nl/win/addins/rdbmerge.htm

Thank you for your help.

I need to update each worksheet rather than combined all worksheet into 1 page.

 

[vletm]

Bear
You uploaded one file - where would be Your named 'summary sheet'?
Need few more files and how are those files named?

 

[Bear]

Bear
You uploaded one file - where would be Your named 'summary sheet'?
Need few more files and how are those files named?

vletm

Thank you for your time to explore the solutions on my questions. Many Thanks.

 

[Marc L]

As a starter : Consolidate data from different excel files (VBA) …​

 

[vletm]

Bear
You could test this sample ...
There is 'Addr'-sheet for filenames
and
[ Do It ]-button to ... do it.

 

[Bear]

Hi vletm

Thank you very much. You really help me a lot. I keep searching methods on the website but I cannot find the solution. You are excel expert. By the way, could you tell me what functions did you use?

 

[vletm]

Bear
I got Your message:
I saved all files in the same folder, however, when I press "do it", it showed "missing file!". I tried to wrote the full path, but it still doesn't work.... and now.
What did You tried to write?
Please, Screenshot or file if there is still challenge.
It seems to work ... or ... not? ... if You keep on searching ?
I tested it again ... it works here!
I didn't use 'functions' ... there are some code.
Try to find it.

 

[Bear]

vletm

It works now. Thank you very much, you make my day.