Need help! Macro to open sequentially named files, copy and paste data into a master file

Hi All,

I've found this site very useful in the past but have an issue I'm not certain how to address. Basically I have a folder which has sequentially named files: "XXXX01.xlsx, XXXX02.xslx, XXXX04.xlsx....". Each XXXX##.xlsx is structured identically and contains 24 hourly data points.

I basically want to create a master file with all hours appended in one file. So essentially I need a macro that will open XXXX01.xlsx, copy a range of data, paste it to a new file, open XXXX02.xlsx, copy the same range of data and append it to the bottom of the pasted data from XXXX01.xlsx.

Any help is greatly appreciated!

copy a range of data?
What range? Which column/s.
I think my understanding is the files are open based on alpha sequence on the folder.

More specifically each of the files XXXX##.xlsx contains columns (Date, type, hour, price volume)
I specifically want to copy Range A3:G28 from each of the XXXX##.xlsx and aggregate all of these files (about 30 of them) into a master file.

How many sheets are in each workbook and if there are more than one sheet, how many sheets within a book do you want to copy? If there is a single sheet, do not worry.

only a single sheet in each workbook

Here's a free add-in that will prompt you for specifics (path, file name pattern, etc.) and then merge the files.
RDBMerge, Excel Merge Add-in for Excel for Windows

Here's various code examples to merge files if you want to create your own.
Copy/Paste/Merge examples

Change this line

Please Login or Register  to view this content.

I put a header on the master sheet, please change the header too.

Wow thanks for the quick replies and all the help!!!! I will try this out and report back.

You guys rock!