Need help in merging excel worksheets from different workbooks

[Ramprakash1990]

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

[#2]

Hi @Ramprakash1990 

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

[#3]

@Ramprakash1990 

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)

[#4]

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.

@L z. 

[#5]

@Ramprakash1990 

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

[#6]

@Ramprakash1990 

In the Power Automate Examples, there's one called Consolidate Excel reports

With a bit of customization it can do what you expect