Merge multiple excel files in one, keeping multiple sheets in VBA

[Lugesz]

Dears,

I have a dataset coming in different files, and these files have different sheets. Structure of Sheet1 e.g. in all files are the same. I would like to merge these files to one master file, by merging the different sheets into the masterfile.

Like this:

Can you please help?

Thanks!

 

[OaklandJim]

Please describe the merge in more detail. Will the two source sheets merge then go away or does the merge happen more than once? Does the merge require that the entire sheet from the two source workbooks get transferred as-is?

 

[John_w]

My understanding is: merge all Sheet1's from the source workbooks to Sheet1 in the Master workbook, one below each other; merge all Sheet2's from the source workbooks to Sheet2 in the Master workbook, one below each other; and so on.

This is fairly easy to do in a VBA macro, but I'd like to see it done with Power Query. I found several PQ examples showing how to merge multiple sheets from multiple source workbooks to a single sheet in a Master workbook, but none showing how to merge them to multiple sheets, with respect to the source sheet names.

 

[Lugesz]

Please describe the merge in more detail. Will the two source sheets merge then go away or does the merge happen more than once? Does the merge require that the entire sheet from the two source workbooks get transferred as-is?

The two source sheets go away. I receive source sheets e.g. every month, so it happens more than once, but I can manage if it is just a one time routine, I manage source files in the back.

It is required that entire sheet should be merged. They have the same structure and dataset.

Thanks.

 

[Lugesz]

My understanding is: merge all Sheet1's from the source workbooks to Sheet1 in the Master workbook, one below each other; merge all Sheet2's from the source workbooks to Sheet2 in the Master workbook, one below each other; and so on.

This is fairly easy to do in a VBA macro, but I'd like to see it done with Power Query. I found several PQ examples showing how to merge multiple sheets from multiple source workbooks to a single sheet in a Master workbook, but none showing how to merge them to multiple sheets, with respect to the source sheet names.

That is the way how I understood. I am happy if it is easy, I could not find, only macros where you merge several workbooks into one workbook. But not found any which would keep sheets as they are.