Modify File Name for Multi-Tab Excel Workbook

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.

Help

Accepted answers

DavidP

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

transpose files.yxzp

All comments

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.

transpose files 1.png

FileA.xlsx

FileB.xlsx

DavidP

Here's the workflow and macro

transpose files.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703