I have one workbook(I will call it "master" ) with multiple sheets, each is linked to different workbooks in same folder. Every month I make new folder and copy the content of old folder to the new one. Basically I have folder with path "/path/1"(number represents the month) and every month I want to change it to "/path/2" ...then from "/path/2" to "/path/3" etc. How would I do that using macro? My idea is that I would have a cell with name of the new folder. And my macro would change the path accordingly. Could you help me?
-
So basically you want to change formula with path, am I right?lowak– lowak2014-08-09 09:53:53 +00:00Commented Aug 9, 2014 at 9:53
-
@Iowak yes, but it's not even real formula in master WB I just have linked cell like =path/1......C1cgnx– cgnx2014-08-09 09:55:17 +00:00Commented Aug 9, 2014 at 9:55
Add a comment
|
1 Answer
Ok, your path should something look like this:
ActiveCell.FormulaR1C1 = "='C:\Users\My_user\Desktop\example_folder\1\[Testowanie_fak1.xlsm]Test'!R1C1"
If you want to change the path so your cells will be linked to a different folder all you need to do this is change the formula just a little bit. In this case I added Month(Date) which will automatically add number with current month (so now it will add 8).
ActiveCell.FormulaR1C1 = "='C:\Users\My_user\Desktop\example_folder\" & Month(Date) & "\[Testowanie_fak1.xlsm]Test'!R1C1"
