merge multiple worksheets based on values in column

Hello all,
I have a workbook with 17 sheets named "data_1" through "data_17". All of the headers are the same in each sheet. I need to create 3 new sheets with the same headers, and merge the 17 sheets based on values in column A.
On every sheet column A has a value of 1 though 10. I need 1-3 to merge on the 1st new sheet, 4-6 to merge on the 2nd new sheet, and 7-10 to merge on the 3rd new sheet.
Thank you for your help.

Hi
see attach

I am getting an error on Ln 18. Run-time error '9': Subscript out of range

Hi, sacwgar

Submit your workbook pls. Don't be shy

I had to remove a lot of rows and sheets to get the size of my file below what's allowed. In reality the information on this sheet goes down to row 64,999. There are also 16 additional sheets (named Data_2 to Data_17), each going to row 64,999.
When the merge is complete, each of the 3 new sheets will have between 300k to 400k rows.

Thank you!

Lots of duplicate records in worksheet, if only unduplicated records are needed, use " UNION " instead of " UNION All " in program.