VBA Code to copy cell range from multiple workbooks?

Hey folks!
I could use a nudge in the right direction here. I have a large document at work that houses a range of values for numerous users, and then separate documents for each user that they fill in themselves. Every week, I have to open the user doc, copy a range of cells, paste into their respective range, and repeat that process for ALL users. It's time consuming and frustrating since I'm sure there's a more automatic way to set this up.

I found some VBA code that'd allow me to copy a range from an open workbook, but I'd like to skip the open workbook part since it sort of defeats the purpose. Additionally, the VBA code I found only allows for one range on one workbook to be copied, where I'd actually need to copy a range from multiple workbooks. So for example:

The main document is saved out by week. The user documents are saved out by month. I'd like to have some code on the main workbook via a macro I can set up to go look at User1's workbook (closed, though), and copy cells A2:F23 and then paste them into cells A1:F22 on the main document. Then, before completing, it would go to User2's workbook, and do the same thing, only paste into cells A21:F42. Then User 3, User 4, etc. etc.

Is there ANY way to do this?

I've attached an example worksheet. Tab 1 is how the individual user documents are set up, and tab 2 is how the main document is set up (you'll notice on tab 2 there are different users, but each week range is the same).

Any help would be greatly appreciated, and if you need more info, just ask! Thanks!

title updated

What's your issue with opening and closing multiple workbooks? Personally, I think it's a much simpler process than messing about with code to process data in closed workbooks. And, if I'm honest, I don't think there's that much of a speed difference.

Your description doesn't match the data in the sample and your description would have you overwriting data.

Regards, TMS

Originally Posted by JLGWhiz

Are the user files all in the same directory?
Is the Directory the same for the user files as for the main document file? If not, what is the directory for the user files?
Do they have a common string in their file name so they can be isolated from other Excel files in that directory?
The ranges in the OP narrative does not coincide with the examples in the attachment, which, if either, is correct?

Heya! In order(anything in CAPS would be replaced by whatever the CAPS stuff says):
1.) Sort of. Each user is saved in their respective supervisor's folder. So it'd be like: C:\Documents\SUPERVISORNAME\USERNAME.xlsx The C\Docs part is the same for everyone though.
2.) Nope! Main Doc file would look something like this: C:\Documents\Reports\MONTH\WEEK.xlsx
3.) Each document is named either for the worker or the week, so they're all different. (Not sure if that addresses your question)
4.) The ranges provided in the original were just made up. The ranges that would need copying are cells A2:I15 on the first tab, and that would go into the same cell range on the 2nd tab. However for a 2nd user, while the first source would remain the same (i.e. copying JANE DOE's sheet, cells A2:I15 still), the paste location for the 2nd tab would be different since I'm wanting it to paste her information to her relevant area. So in this made up scenario, JANE DOE's information would be pasted to A18:I30 on the 2nd tab.

Hopefully all of that made sense!

You misunderstand me. If you use a macro to cycle through the workbooks, you've removed the drudgery regardless of whether you (the code) opens the files or not.

Regards, TMS