Referencing a closed workbook

Change the "weight" cell that's in summary workbook and the macro runs by itself for closed workbooks.. I guess, I'm trying to save a step of pressing the key for macro but now that I think about it...it's not really needed.

So with that code, if I change the weight cell that's in summary workbook and run the macro, other closed workbooks should run in background and update it's values...and hence I should see the summary workbook pull new data. Am I correct?

If so, I'll definitely give it a try tonight. Thanks!

Originally Posted by blacktrek

Change the "weight" cell that's in summary workbook and the macro runs by itself for closed workbooks.

Definitely...just instead of putting the code on the "This Workbook" object...go to the object that matches the tab name for the worksheet that you keep the "weight" cell on. This put this code there.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False   'This turns off screen updating and speeds up macro
    Workbooks.Open Filename:="L:\Folder\Filename.xlsx"    'Opens the file (rinse)
    ActiveWorkbook.UpdateLink Name:= _
        "L:\Folder\Filename.xlsx", _
        Type:=xlExcelLinks    'Updates links (lather)
    Workbooks.Close Filename:="L:\Folder\Filename.xlsx"    'Close the file (repeat)
    '*********************
    'Copy paste the rinse/lather/repeat sections ;) here and change the file names for each file you have to open. (delete this line)
    '*********************   
    Application.ScreenUpdating = True    'This turns screen updating back on
End Sub

You could put the code in both places....the "This Workbook" one would make sure everything is updated when you first open the workbook and the one on the worksheet tab object would run if you make changes to the "weight" cell. But.....is that overkill? Are you just running the same update twice? Sorry...I can't tell without knowing how you use the workbooks!

---

If you found this post to be helpful, or if it at least brought a smile to your face, please say "Thank You" by clicking Add Reputation. Thank you!