Macro to combine multiple Excel sheets from the latest file picked by Directory Tool

[Shakil1]

Hi Alteryx Community,

I'm looking for macro to combine multiple Excel sheets from the latest file picked by Directory Tool, where only one sheet has headers and the others do not.

I’m working with an Excel file that contains multiple sheets:

• One sheet (e.g., "Full Data") contains column headers and full data.

• The other sheets (e.g., "DataOnly1", "DataOnly2", etc.) contain only data without headers.

Each sheet has the same structure (same number and order of columns), but only one sheet provides the correct column names.

I would like to create a macro in Alteryx that:

1. Takes sheet names dynamically as input (e.g., from a list).

2. Reads each sheet, whether it has headers or not.

3. Applies the headers from the “Full Data” sheet to the data-only sheets.

4. Combines all sheets into one unified dataset.

Once the macro is built, I plan to use it inside my main workflow and connect it to the Directory Tool to loop through multiple Excel files and process all their sheets accordingly.   Below is the example screenshot for your reference.   Thanks!

[caltang]

I'm glad to see you try your hand at it - but this is your original post: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Urgent-Macro-to-combine-multip...

Updating the comments there might be more useful to give users context rather than starting a new thread.