Quote:
Originally Posted by Markzas
Hello,
I am trying to copy a value of one cell from one spreadsheet to a cell of another spreadsheet.
|
From what I gather, you have links between two separate spreadsheet files, and you can't see the updates appearing in the second until you save and re-open the second?
Spreadsheet#1 = master spreadsheet
Spreadsheet#2 = links to values in Spreadsheet#1
Excel appears (from what I can see) to only update the links when you hit Save, hence the problem you can see.
One thing I can think of to help you is to put a small line of code into Spreadsheet#2 so that any links are updated the moment you open it, instead of waiting for you to save it.
In Spreadsheet#2, you will need to:
- Go into Macros (Tools/Macros in Excel 2003, Developer/Visual Basic in Excel 2007 - if Developer menu doesn't show, click the round button top left, click Excel Options, and under Popular, tick the Show Developer tab in the Ribbon option)
- Under VBAProject(Spreadsheet#2.xls), double-click on ThisWorkbook
- Drop down the list where it says (general) and select Workbook
- By default it should select Open in the right-hand drop down and give you a Private Sub Workbook_Open()
- Between the Private Sub Workbook_Open() and the End Sub, copy and paste in the following line:
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Congratulations! You've written a macro. Now save Spreadsheet#2 and close it. Make changes to Spreadsheet#1 then save and close.
When you open Spreadsheet#2 with the macro in it (assuming macros are enabled) it should automatically update the links for you.