Modify File Name for Multi-Tab Excel Workbook

[KyleCollins]

I have two separate files that have multiple tabs with same sheet names in each file. I would like to change the output to have multiple files with just two tabs. 

For example File A has tabs called Person1, Person 2, Person 3... and File B has tabs called Person1, Person2, Person3...

I would like the output to a file for each Person that has two tabs (respective File A tab and File B tab)

Note that the Files have different schema that I would like to keep as is, just a change in the setup of the file output.

Any suggestions would be greatly appreciated. 

[DavidP]

In order to achieve this, I utilized 2 macros. I'll talk you through it.

A browse tool loads the list of filenames (FileA and FileB) from a specified folder and the list of sheet names is loaded for each file with a Dynamic Input tool.

The full path for each file is then updated so that there is a unique file path for each sheet in each file.

The first macro then loads all the data from all the sheets and also the file path for each.

A bit of Regex magic swaps the sheet name and filename for each file path and the data is then prepared for the 2nd macro that writes the data to each of the new filename/sheet name combinations.

Let me know if you have any trouble getting it working.

[DavidP]

Here's the workflow and macro

[DavidP · Accepted solution]

Sorry, I think I didn't attache the final version - here it is

[KyleCollins]

Thanks David, 

This does exactly what I needed, cheers!!