MERGING SEVERAL TABS TOGETHER - 100+

[iamviraj13]

Hi All,

I am attaching a file that has around 5 tabs as an example to merge. I have tried using Dynamic Input and have had success in merging 20-30 tabs together.

However, just wanted to see how can I merge more than 100 tabs all together? Sometimes the Dynamic Input gives a Schema error.

Any solution would be appreciated. Thanks!

[iCFO]

Have you looked into this post?

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

I also sometimes use VBA to do things like splitting tabs to separate files, renaming tabs or combining tabs of the same schema to simplify multiple tab management.

https://www.exceltip.com/cells-ranges-rows-and-columns-in-vba/consolidatemerge-multiple-worksheets-i...

[Maskell_Rascal · Accepted solution]

Hi @iamviraj13 

Here is how you can do it. 

You'll first want to read in your Excel File to include the Full Path as a field and also only read in a list of sheet names. 

The use a Formula tool to create updated FileNames that include the full path and the sheet name. 

This will then be fed into a batch macro that will read in and combine all the tabs. 

The macro itself is configured for the needs of the sample file you provided, so the template it uses starts the data import on row 9 but can be changed if needed. You can also include the Full Path or File Name as a field, so you know which tab the data is coming from. 

Attached is a zipped copy of the sample workflow and the macro for you to try out. 

Let me know if this works for you. 

Cheers!

Phil

[iCFO]

Great macro solution @Maskell_Rascal ! I am definitely going to be using this myself as well.

[iamviraj13]

Thanks, @Maskell_Rascal for the detailed explanation.