Combine all excel sheets & field name with the sheet name using macro

Question

I have data in 2 excel tabs as follows:

I want to use macro that gives me following output:

So basically I want to do the following via macro
1) Combine all the excel tabs 
2) Combine field name with the sheet name &

3) Add an additional column that contains sheet name

Test_CFNSN.xlsx

SPetrie · Accepted Answer

The type of macro you want is a batch macro. I recently created a macro that mostly does all this already so it was a pretty quick change for your ask. Feed it the full address of the file with its sheet name and also the actual sheet name by itself to be used with naming and labeling.

SPetrie · Accepted Answer

That control parameter doesnt need to be attached to anything. Its being used as a reference shortcut.

I changed the name of that parameter to something easy for me to identify and then I can reference it in a formula tool using the reference shortcut.

Once its setup, you can make a reference to it in the formula or other tools.

This is where I use it to populate the SheetNames column

and this is where it is used to rename the columns.

Chirag_KRCPL · Accepted Answer

@SPetrie Thankyou for your response!!