How to combine different tabs into one but first tab has redundant rows?

Question

Hi! I have been using a control parameter (macro) to stack various sheets into one excel output. Recently I have this report (screenshots attached) to try to combine various sheets into one but my Sheet1 has some redundant top rows and I cannot seem to match by column position or name.

Is there a way to automate this by just adding the input file once rather than sheet by sheet because my actual data might have >50 sheets in one excel file. There are also redundant tabs in the excel. Apologies I am unable to upload the excel file here.

Thanks in advance!

AkimasaKajitani · Accepted Answer

Hi @jp_jen

I have 2 solutions.

1.
You read all the sheets and then delete the data that you don't require.
In this case, you have to enable "First Row Contains Data" option. But this case don't require macro.

2.
You read all the sheet once, and count the record position of "S/N" row that should be in the field name, then reopen them again using "Start Date Import on Line" option to skip the redundant rows.