Looping through multiple files in a folder and extracting data from a specific tab

Hi Guys,

Can anyone help me with the below?

I have a folder that has >300 spread sheets that I have saved over the years. The layout of each spread sheet is practically identical just with different data held within each.

I would like to be able to run a macro in a new spread sheet that opens each excel file, navigates to a tab called “Volume” and then copies the data held in cells A2:E10 into the new workbook and puts the name of the file in each cell in column F next to the data that has just been copied. Once this is done, can the file be closed and then the next file opened and the same data copied to the new workbook, but in the row below the previous copy so it’s on some sort of loop until it has gone through all files in the folder?

Is this possible?

Originally Posted by arlu1201

Will the range A2:E10 be the same for all workbooks? Also, will the row 10 be the last all the time? Or do you want it to pick up the last populated row in that file?

Yes, the range will always be a2:e10 and row 10 will always be the last row of data in all the files.