Copy Contents Multiple Workbooks and Worksheets

[CoSinus]

Dear Community,
I have a lot of data that I need to analyze and would like to synopticize it.

For this I would like to take an empty Excel sheet and then select the folder after calling the macro, i.e. not all sheets in the folder should be selected automatically.
We could also start with one excel sheet that contains the data. Preferably the one with the highest or lowest number. Whatever works better.

After selecting the Excel Files the markro should then copy the contents of the sheets with the same name of each workbook next to each other.
There should be one column of space between the copied contents of each sheet.
For this Sheets have to be created in the new Workbook.

The copying should be done in such a way that the contents of the sheet with the highest number are at the beginning and the contents of the other sheets copied next to it are copied in descending order.

A great luxury would be if on each sheet a line could be inserted, which carries the sheet names but only if it is not too much effort.

I have uploaded sample files.

The files
7 Summary.xlsx
8 Summary.xlsx
contain sheets with the same names to be copied side by side.

The sheet 'Resulting.xlsx' then shows how the merging should look like.
That the sheets in the examples have the same number of columns is a coincidence.

The names of the sheets follow the logic:
1 Summary.xlsx
2 Summary.xlsx
3 Summary.xlsx
4 Summary.xlsx
etc.

A workbook can contain up to 50 sheets.
There are also workbooks where only some of the sheets are present and not all.

If there are any questions I am glad about a feedback
Any othe soultions are welcome.

I thank you on my knees for a solution.
Greetings
Tom

[CoSinus]

Here is a Code that does the following:

1. Prompts the user to select the folder containing Excel files to merge
2. Prompts the user to select a save location and file name for the merged file
3. Creates a new workbook to merge the Excel files into
4. Loops through each selected Excel file and opens them
5. For each worksheet in the opened Excel file, checks if the worksheet already exists in the target workbook or not
6. If the worksheet exists, copies the data from the opened worksheet and pastes it in the corresponding worksheet in the target workbook
7. If the worksheet does not exist, creates a new worksheet in the target workbook and copies the data from the opened worksheet
8. Saves the target workbook with the specified file name and location
9. Closes all workbooks and displays a message indicating that the Excel files have been merged successfully.

Please Login or Register  to view this content.