VBA copying multiple sheets to multiple sheets

[Jumparound]

Hi,

I have been wrestling with this for a while trying a number of bits of code I have found online but can't find anything that matches what I want to do or can change enough to make it work for me.

I have workbooks of data in a folder and I want to copy a range of data from each one into different worksheets of a master sheet in the same folder. The files to copy from are labelled as follows:
TS Weekly Report - SiteA Jan
TS Weekly Report - SiteB Jan
TS Weekly Report - SiteC Jan
TS Weekly Report - SiteD Jan
TS Weekly Report - SiteE Jan

The file to copy to is:
TS Weekly Report - UK Jan

All these workbooks are in C:\Users\User\Desktop\Test

All the files to be copied from have a worksheet called Site Summary and the data to be copied in all these is in C9:D27
I would like to paste these data into the same place in a worksheet for each site in the UK Jan workbook. So it would open SiteA workbook copy the data from C9:D27 in Site Summary and paste into the UK Jan workbook in worksheet SiteA in cells C9:D27. Then open SiteB workbook copy the data from C9:D27 in Site Summary and paste into the UK Jan workbook in worksheet SiteB in cells C9:D27 and so on.....

Can anyone help with this?

 

[mumps]

I'm glad it worked out. @Fluff: Thank you for your suggestion. Would you have any idea why the original code I posted worked for me but generated an error for the PO?

 

[Fluff]

Dir will look in the active directory, unless told otherwise.
So initially that will be your default xl path, but if you save a file to another location (such as C:\Users\User\Desktop\Test\) that will then be the active directory.
I suspect that you had saved a file to the test directory, hence that is where Dir looked, but the OP may still have had the default path as active, hence it Dir was looking in the wrong place.

HTH

 

[mumps]

That makes perfect sense. Thank you once again. By the way, congratulations on being appointed as Moderator.

 

[Fluff]

By the way, congratulations on being appointed as Moderator.

Many thanks