Copy and Paste Between Two Workbooks

[Gary24]

I have 6 files saved on a local drive (in a folder called Automobiles) and would want them to be added to a master workbook (master_file) in their specific worksheet. Each file has its own worksheet and I would want to copy everything from row 2 to the last row from the files and paste them into row 2 of each worksheet.

Example:
6 files (saved on a C Drive - automobile folder)
Car1.xlsx (will be pasted into car1 worksheet)
Car2.xlsx (will be pasted into car22 worksheet)
Car3.xlsx (will be pasted into car3 worksheet)
Car4.xlsx (will be pasted into car4 worksheet)
Car5.xlsx (will be pasted into car5 worksheet)
Car6.xlsx (will be pasted into car56 worksheet)

I would greatly appreciate your help on this issue for me. Thank you!

 

[mumps]

What is the full path to the folder containing the 6 files? Are the 6 files the only files in that folder? What is the name of the sheet in each file that contains the data you want to copy? It looks like the sheet names in the master file are named based on the source file name. Is this correct?

 

[Gary24]

What is the full path to the folder containing the 6 files? Are the 6 files the only files in that folder? What is the name of the sheet in each file that contains the data you want to copy? It looks like the sheet names in the master file are named based on the source file name. Is this correct?

F:/Automobiles is the full path (would it matter if the path changes later?)

Yes the 6 fields are the only files in this folder

the names might change but for example purposes
car1 - lexus
car2 - benz
car3 - Mazda
car4 - fiat
car5 - golf
car6 - bmw

 

[mumps]

Are you saying that in Car1.xlsx the sheet name is "lexus" and in Car2.xlsx the sheet name is "benz", etc.? If that is true, will the sheet code name in the 6 files always be Sheet1, in other words will the sheet containing the data you want to copy always be the first sheet in the workbook? What is the name of the destination sheet in the master workbook?