topic Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers in Alteryx Designer Desktop Discussions

Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Shakil1 — Wed, 18 Jun 2025 18:58:19 GMT

Hi Alteryx Community,

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:

Takes sheet names dynamically as input (e.g., from a list).
Reads each sheet, whether it has headers or not.
Applies the headers from the “Full Data” sheet to the data-only sheets.
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.

Sample data sets are below. Thanks!

Person Number First Name Last Name Department

1001	John	Doe	HR
1002	Alice	Smith	Finance

1003	Bob	Jones	IT
1004	Sara	Khan	Admin

1005	Eva	Wilson	Sales
1006	Tom	Hall	HR

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

apathetichell — Wed, 18 Jun 2025 20:48:13 GMT

input data --- only sheet names mode.

use a formula tool to replace '<List of Sheet Names>' in your full path with the sheet name.

filter tool ---> select your headers sheet name.

batch macro ---> read in headers sheet.

back to filter tool>

second batch macro -> read in all other sheets. Read from line 1 ---> do not read in headers.

interface designer ---> union by name.

main workflow ---> dynamic rename (right column names) to output from batch macro 2. batch macro 1 has the column name.

if you need to union in data from batch macro 1 --- use a union tool.

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Shakil1 — Thu, 19 Jun 2025 09:03:17 GMT

@apathetichell

Thank you for the response!

I'm not sure how to do that, could you please create a basic workflow and help me with it?

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

alexnajm — Thu, 19 Jun 2025 12:03:07 GMT

@Shakil1, @apathetichell has outlined it well. There are also many Community articles with batch macros that you can leverage as a foundation and tweak to your requirements, like this one: The Ultimate Input Data Flowchart.

Please try it and come back with troubleshooting questions!

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

caltang — Thu, 19 Jun 2025 12:37:36 GMT

Just to help you out.

@Shakil1 - what @apathetichell wrote is the way to do it. But as a beginner, I understand you may not follow it well enough, so I made a different version to achieve what you want.

Though some caveats - I am assuming your data structure is the same as you described, so I am simplifying a lot more.

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Shakil1 — Thu, 19 Jun 2025 13:25:43 GMT

Thank you so much! @caltang @alexnajm @apathetichell

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

KGT — Fri, 20 Jun 2025 01:24:53 GMT

I'm pretty sure this is the most appropriate use case of either Cameron's Read all Excel (below), or the CReW Macros version. Especially with only one tab having headers.

https://community.alteryx.com/t5/Engine-Works/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-All-Excel/ba-p/5558

topic Re: **Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers in Alteryx Designer Desktop Discussions

**Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers

Re: **Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers

Re: **Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers

Re: **Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers

Re: **Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers

Re: **Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers

Re: **Urgent ** Macro to combine multiple Excel sheets where only one sheet has headers

topic Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers in Alteryx Designer Desktop Discussions

Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers

Re: Urgent Macro to combine multiple Excel sheets where only one sheet has headers