Macro In Excel - Need Help 1

[jksb]

I've set up a macro in Excel linked with a control button that will SaveAs when clicked and will make a duplicate of the workbook. My problem is that I have linked the workbooks together so that totals will carry from one month to the next; what's happening is that the new workbook is still linked to the one the old book was, not to the new workbook. Here is what I have:

ActiveCell.FormulaR1C1 = "=[Jan.xls]Week4!R5C10"
ActiveCell.FormulaR1C1 = "=[Jan.xls]Week4!RC[2]


I'm not sure how to tell it to create a new link to the workbook I created the new workbook from. Also, what is the R5C10 and RC[2] mean? The cell I'm linking to is J5.

Thank you!

Jackie

 

[Ruairi]

you would change the [jan.xls] in the formula to the name of the workbook you want to link to. The R5C10 means Row 5 Column 10 (J10). Im not sure what RC[2] would be, is J5 a named range maybe?
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[jksb]

Thanks! The only problem is that I will create a new workbook monthly; so each month the link will change to the previous month (the one I created it from). I can use Links/ChangeSource but was hoping to make it more automatic, i.e. when I use the control.

Jackie

 

[Ruairi]

I think you should be able to use code like this in the macro.

ActiveSheet.Range("A1").FormulaR1C1 = "='c:\[Feb.xls]Sheet1'!R5C10"

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[jksb]

No, it doesn't do it. Example: I'm in Aug.xls, save it as new.xls, change name to Sept.xls, linked to Aug.xls; make new workbook from Sept.xls, change name to Nov.xls, and the link is still to August.xls, not to Sept.xls, which is where I need it to go.

Thank you for your help!

Jackie