Copying from multiple files to master file (specific sheets)

[ll4321]

Hi All,

I'm a VBA newbie.. Tried to search around but seems couldn't find a solution for my problem. Would be grateful if someone could help...

What I'm trying to do is as follows:

1. I've got a few files stored in the following path say:
C:// desktop/ 2022Q1 /source/ abc.xlsx
C:// desktop/ 2022Q1 /source/ def.xlsx
C:// desktop/ 2022Q1 /source/ ghi.xlsx

in each of the above excels, they all have a number of sheets...
I'd like to copy and paste the content from sheet abc (from file abc.xlsx), sheet def from (file def.xlsx) etc. to the master file (say master.xlsm) which consists of sheet abc, sheet def
the aim of the above action is just to simply copy and paste from the source file (from the first row and column to the last row and column ) into the master file

2. the above action would repeat for the next quarter where the source files will be saved in the following path:
C:// desktop/ 2022Q2 /source/ abc.xlsx
C:// desktop/ 2022Q2 /source/ def.xlsx
C:// desktop/ 2022Q2 /source/ ghi.xlsx

My question is : are there any easy methods to repeat the above for the upcoming quarters or the only method is to update the path in the vba code manually?

I've searched around and seems like someone suggested to use Application.GetOpenFilename ...
however, given i need to paste the sheets in specific sheets in the master file (can I still use Application.GetOpenFilename to perform the above action)?

Thanks in advance!

 

[GlennUK]

Have you thought about linking cells to the source workbooks, then you just do Data/Edit Links/Change source to point to the new quarter when necessary (well, do that command per source file).