Compounding multiple Excel sheets in the same workbook into one sheet

Question

I have an Excel workbook with 36 different sheets in it that I receive every 2 weeks, the sheets have common headers across all tabs and unique headers which are different on each tab but each record has a unique ID which can have several records.

What I'm trying to do is strip the unique IDs from all of the sheets then pull the data through from each of them onto one sheet with all of the common headers as well as all of the unique headers.

I was considering using the code from the below post to import it into Access connect the tables and export it back into one sheet in Excel but the code doesn't work, I get the run-time error that: field "F1" does not exist in destination table error and I can't see how they've fixed that issue.

Importing multiple sheets from an excel file into multiple tables by sheet name

I'm not sure that's the best way to achieve what I'm to.

Answer 1

Don't import the sheets, link them.

Then create a straight select query using the linked table(s) as source and where you rename (alias) fields like F1 to something meaningful. Also apply simple filtering for invalid records and conversion of field values as needed.

Then use this/these query/queries for your further processing.