Loop to copy the same rows from multiple files to a master file

Hi! I am really quite new to macros and am trying to get up to speed

I am trying to create a macro which copies an individual row from over a hundred separate excel files (all formatted identically) and pastes them into a separate master file with them all listed consecutively. I assume I need a for loop but cant work out how to get it to loop through separate files. I then have trouble coding to get the pasting to move down a row on the master sheet.

Any help would be much appreciated!!!

Thanks

Alex

Hi- thank you for replying.

Each workbook contains daily summary information for 16 different water monitoring sites (WM-01 to WM-16). I need to create a separate workbook which contains every day of information for just 1 site (e.g. WM-14).

For example, in the simplified attached workbook, I need to copy rows 16-18 (site WM-14) and paste this information into another workbook. This process needs to be repeated for all the files in the folder (about 100 and all formatted identically) in order to obtain all of the information from all days of monitoring for just site WM-14 in one 'master' workbook.

I therefore need a code to loop through each file, copying rows 16-18 of each and pasting them consecutively one after another in a master workbook.

All help, greatly, greatly appreciated!
Thank you!

Thank you so much for this. It works well- I could never have written that myself!

The only issue is that I would like to be able to get the code to loop through every Excel file in the folder (i.e. get the source file to change). However, when the code runs through these files, the information from each file needs to be copied to a common (master) sheet for each site (e.g. WM-01) (i.e. the sheet created during the first loop), rather than opening a new set of sheets for each file.

For example, row 4 from both the attached files ('SWM_example1', 'SWM_example2') needs to be copied to the same 'WM-02' sheet (which was created by your fantastic code, shown in your example, 'SWM_example(bvj)'), rather than creating a new 'WM-02' sheet everytime the code loops.

In my mind you are already a genius, but if you can help me with tweaking the code to do this, you will be STELLAR.

Many thanks