Referencing a closed workbook

Hello! This is my first post. I am urgently doing something and because I'm not smart enough, I can't seem to come up with the solution.

I was hoping if anyone could help me.

Right now, I have multiple workbooks (summary, 2003, 2004, 2005, ..., 2015).

There's this "weight" cell in summary workbook. If both summary and 2003 workbooks are opened, and I change this weight cell to a different #, the 2003 workbook re-calculates it's values, and summary workbook also updates based on the re-calculation in 2003. So it auto updates using the weight.

If 2003 workbook is closed, and I change this weight cell in summary workbook, I cannot see the automatic update. Nothing happens. Because I need this relationship to work between summary and 2003, summary and 2004, summary and 2005, and so on...for now I MUST leave open each and every workbooks from 2003 to 2015 in order for summary page to take effect the modified weight.

Is there a way to leave 2003 to 2015 workbooks all closed, and the summary workbook do it's magic?

I hope I was clear with the question.

Thanks for reading!

The only way I know to "autoupdate" data from closed workbooks is to use the sumproduct function (instead of just making a cell in the Summary workbook equal a cell in another workbook...references like that won't autoupdate).

The other option is to write a macro so that when you open the summary workbook, it opens the other workbooks, refreshs the links, and closes those workbooks. Depending on the size of your data tables, that might be the easier/faster options so you don't have to rebuild all those calcualtions in the summary workbook.

---

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!

Originally Posted by acroley1

The other option is to write a macro so that when you open the summary workbook, it opens the other workbooks, refreshs the links, and closes those workbooks. Depending on the size of your data tables, that might be the easier/faster options so you don't have to rebuild all those calcualtions in the summary workbook.

This sounds like something I can do!

Do you know where I can find a template for this purpose that opens workbooks, refresh, and closes? I'm not into coding, but I do know how to set the macro. Is this all happening in the background in a short time?

Can I set the macro so that it occurs when a cell or cells are changed?

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!

BTW, I am fairly new to coding so even if the code looks intimidating , you can totally be walked through getting it up and running on this board! This site has taught me so much!!!!