Combining multiple excel sheets into one excel file and updating it each time there are changes.

Copper Contributor

i have 5 excel workbooks with all the sheets named the same. what I want to do is combine the 5 excel files into one workbook with the sheets in each of the excel file file merged together with the same sheet name respectively. for example in excel file 1 there are sheet names sales and items and in excel file 2 there is also sheet names sales and items. what I want to do is combine the two excel files with each other so that excel file 1 sales and excel file 2 sales are merged together and excel file 1 and excel file 2 items are merged together. Is there a way for this? And I want the excel file to be updated on a regular basis maybe weekly once or twice if there are any changes into the 5 excel files

3 Replies


In general that's job for Power Query if you consider such option (your tag is VBA).

Hi, then is it possible to connect the sheets that are in each of the workbook together as mentioned in the query without the use of Power Query