Forum Discussion
Need help in merging excel worksheets from different workbooks
Hi all
I want to merge worksheets from different workbooks, but I cant merge more than two sheets into a master file if the name of the sheets are same. How do I go about this?
Example: I've 3 workbooks titled workbook 1, workbook 2 & workbook 3, in which I've named the sheets ACT, when I try to merge the sheets form different workbooks I'm able to merge workbook 1 & 2, not workbook 3 sheet as an error message pops up stating the sheets name are same.
 
Thanks in advance
Rgds
Ramprakash
Replicated 3 workbooks similar to yours, each containing a sheet named ACT
The attached sample has a query that Combine/Append (not merge) Sheets named ACT
All query steps done with the User Interface only
I don't want to merge all the data from multiple workbooks into a single sheet, instead I want to move worksheets of same name form different workbooks into one workbook.
OK. Not sure I can help further as it's not doable automatically with Power Query (one query = one output)
If you run Excel 2021 or 365 look at Export Data From Power Query into Multiple Excel Sheets based on criteria. You would use the query I shared then use the above VBA solution to split your sheets combined as a single table
If you struggle replicating the process have a look to Combine All or Selected Excel Sheets or Tables in Folders (inc. a workbook template that does what you want after enterring a few parameters)
In the Power Automate Examples, there's one called Consolidate Excel reports
With a bit of customization it can do what you expect
