Create macro to copy data from multiple files

[Jd85]

My employeer wants me to find a way to create a macro that can open an excel
file, copy a line, paste it into a master file, and then move to the next
file and repeat. There are approximately 400 files/master file. All I've been
able to do is to record a macro that opens the same file. I am new to the
macro world and would appreciate a Laymen's guide to helping me fix this
macro . Thank you.

 

[Jd85]

I'm going to add just a few details of the copying just to be a little more
clear in what I'm hoping for.

1) Open excel file 1
2) Copy A2$:BR2
3) Insert Copy into "Master File"
4) Close excel file 1
5) Open excel file 2
6) Repeat step 2
7) Insert Copy in next row of "Master File"
8) Close and repeat.

Also, there might be a slight problem opening a file from the File->Open
menu. When trying to open a file that way Excel prompts a TIW (text import
wizard). I don't know if that complicates things more.

 

[Ken Snell \(MVP\)]

See this article:

Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFolderFiles