Import data from several closed workbooks to a single sheet in an open workbook

Hello all.
I have looked through the posts for help but I did not see anything I could adapt for my specific need.

I need to import the data from a specific sheet (same named sheet on all closed workbooks) to a sheet in an open workbook. All the columns are identical in every workbook but the number of rows is variable, so the data from each subsequent workbook must be appended to the end of the current data.

Whenever a button is pressed, this macro will clear the sheet, then import the data starting in A3. The workbooks are in different folders but they all have the same name, so some sort of explorer window will probably be needed to actually select each file.

Any help would be appreciated.

Thanks,
Andrew

Hello Andrew,

Ron De Bruin covers how do this on his site. Click on the link below to go to his page on this topic.

Copy a range from closed workbooks (ADO)

Hi
Assuming you have 10 columns and sheet name as sheet1, try the macro in the attached file. save it inside the folder containing files for data extraction. works on the contents of a single folder only. Pulls data from closed file
ravi