Copying and Pasting Data from Several Workbooks

[tobermory]

Hello there,

I have 7 spreadsheets in folder S:\MHS\Review

I would like to copy and paste the data from these 7 spreadsheets to a master file (which ideally would be the 8th spreadsheet in the same folder).

Very basically...

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

copy and paste to master file

Each spreadsheet is about 200 rows, so the second set of data can be dumped below the first set of data and so forth.

I'd be capable of this, if the 7 spreadsheets had the same constant file names, however the file names do change to reflect new versions. All of the sheets are named Sheet1 within the 7 spreadsheets.

Would someone be generous enough to provide me with some code that could do this?

Many Thanks

 

[NdNoviceHlp]

Have a look through this thread. Seems similar and highlights how using an array based solution will be much quicker than using copy and paste. HTH. Dave
https://www.mrexcel.com/forum/excel-questions/1077924-loop-through-formula-macro.html