Multiple files with multiple sheets into one file with multiple sheets

[TJ2]

I have a number of individual files with unique names with numerous unique tabs containing the same fields. All of these individual files needs to be combined into one file and all of the individual sheets needs to be combined sheet by sheet. For example: Three files with three sheets each combined into one file with each of the three sheets appended from the individual files.

[messi007]

@TJ2,

You can do this using a batch macro:

1- Update the file path

2- Update the sheet name using the dynamic read

3- Update the macro read properties

3- Call the macro

Attached the workflow,

Hope this helps!

Regards

[skeen503979]

@TJ2 

This should work for you.

[mceleavey]

Hi @TJ2 ,

I've built some tool to do this automatically.

I've attached the tools and an example.

Open the example, simply change the folder and filename in the directory tool and that should load everything for you.

M.

[TJ2]

To clarify, I need the output file to have the data combined as shown: One file with three different tabs appended together

[mceleavey]

Hi @TJ2 , that's right.

The method I gave you will essentially load in all files and all sheets and union them together, which creates the output you've specified.

If you only wish to load in certain sheets, filter them after the multi-sheet macro.

M.

[TJ2]

Thanks so far, cool macro. I got the data to be combined following the instructions, but I need a separate tab to be created for each tab the data came from. This is combining everything into one tab. The output file should have three separate tabs with data appended from the three separate files.

[jpdwhite]

Thanks for a great macro, I was searching around for ages before I found this post!

Quick question though, I've added the file name to the output data, but I can't work out how to add the sheet name, could you help please?

Thanks!

[DiattaF]

Hello, is there a follow up regarding this please. 

I have a similar situation where I have 4 different inputs, and I wish to combine them into one file with 4 different tabs.

I understand the macro should work however is there a way to show the step by step please thanks.