Macro to Merge many files to one

Macro to Merge many files to one


I have excel files in a folder, All files have same no of columns,
I need a macro that copies the data from all files in New file and save new file in different location.

My data in File 1 :

A1 = 1XYZ, B1 = 1ABC C1 = 1BHY
A2 = 1BHU, B2 = 1ABD C2 = 1BNJ

My data in File 2 :

A1 = 2XYZ, B1 = 2ABC C1 = 2BHY
A2 = 2BHU, B2 = 2ABD C2 = 2BNJ

Expected Output :

A1 = 1XYZ, B1 = 1ABC C1 = 1BHY
A2 = 1BHU, B2 = 1ABD C2 = 1BNJ
A3 = 2XYZ, B3 = 2ABC C3 = 2BHY
A4 = 2BHU, B4 = 2ABD C4 = 2BNJ

Kindly do the needful.