Hi excel geniuses,
Our team has about 70 files that we need to manually update every month, and I thought it was time to introduce some macros so we could save a ton of time. The only question is if it's possible!
The files are located in folders setup as shown below:
Folder - REGION
∟ Sub Folder - COUNTRY
∟ Excel file - vs. Prior Forecast (comparison 1)
∟ Excel file - vs. Prior Year (comparison 2)
∟ Excel file - vs. Prior Month (comparison 3)
Each file has a "Control" sheet that contains the dimensions that need to be updated every month. The Control sheet of each file has the same layout but different values.
Is there any possible way to create a macro that would copy and paste values from a master Control sheet (3 masters - one specific to each comparison period) to the Control sheet of each of the Excel files, without breaking any references to the Control sheet within the files?
Also, if the above is possible, what would be the best way to set up the master Control sheet so that the appropriate comparison can be copied and pasted? Ie: would it be better to have 3 different master files, or would it be better to have one master file with 3 different sheets?
I know this can be hard to imagine so I can provide more info as needed.
Thank you so much in advance!!
Our team has about 70 files that we need to manually update every month, and I thought it was time to introduce some macros so we could save a ton of time. The only question is if it's possible!
The files are located in folders setup as shown below:
Folder - REGION
∟ Sub Folder - COUNTRY
∟ Excel file - vs. Prior Forecast (comparison 1)
∟ Excel file - vs. Prior Year (comparison 2)
∟ Excel file - vs. Prior Month (comparison 3)
Each file has a "Control" sheet that contains the dimensions that need to be updated every month. The Control sheet of each file has the same layout but different values.
Is there any possible way to create a macro that would copy and paste values from a master Control sheet (3 masters - one specific to each comparison period) to the Control sheet of each of the Excel files, without breaking any references to the Control sheet within the files?
Also, if the above is possible, what would be the best way to set up the master Control sheet so that the appropriate comparison can be copied and pasted? Ie: would it be better to have 3 different master files, or would it be better to have one master file with 3 different sheets?
I know this can be hard to imagine so I can provide more info as needed.
Thank you so much in advance!!
