Feel free to post what you come up with so someone can check it for you.
I did end up using your suggestion - starting with the Macro recorder, testing and tweaking, recording some more, getting guidance from Mr Excel.com, more testing and tweaking, and this is what I ended up with.
These departmental financial projection files will be updated monthly (with new files names) so I set-up my summary workbook to link to the other workbooks using INDIRECT() functions (along with INDEX() and MATCH()) as I would like the links to be dynamic. Value & "\" & _ str File Name Trim & " - VALUES - " & Format(Now, "yyyymmdd-hhmm") & _ ".xlsx", File Format:=xl Open XMLWorkbook, Create Backup:=False ' Windows(wb Summary. Activate ' End Sub Sub Close Group Files() ' Dim int Counter As Integer Dim arr File Names As Variant Dim wb Summary As Workbook Dim wb To Close As Workbook ' arr File Names = Array("B10", "B13", "B16", "B19", "B22") Set wb Summary = Active Workbook ' For int Counter = LBound(arr File Names) To UBound(arr File Names) Set wb To Close = Workbooks(Sheets("MAIL").
The links work great as long as the workbooks I am linking to (using the INDIRECT() function) are opened. The amazing, amazing people right here have created a free, essential add-in for Excel, called Morefunc. Value str File Name Trim = Left(str File Name, Len(str File Name) - 5) ' Worksheets("SSP Summary").
When I try to put the focus on the correct tab ("Linked Sheet") I get the following error message.
Locking a link prevents the object from being updated by the original application file, such as an Excel spreadsheet file.
But if they are not opened then all of my INDIRECT() function links return a #REF! If I were to "hardcode" the links: in my summary file (workbook) then I am given the option to update links when I open the file. One of the 66 new functions they give you is INDIRECT. Protect Drawing Objects:=True, Contents:=True, Scenarios:=True ' End Sub Sub Copy And Save() ' Dim wb Summary As Workbook Dim wb Sum Values As Workbook Dim str File Name As String Dim str File Name Trim As String ' Set wb Summary = Active Workbook str File Name = wb Summary. Copy Set wb Sum Values = Active Workbook wb Sum Values.
Even then the INDIRECT() function links are not updated. By correct this I mean that I would like to open my summary file (workbook) and have any and all links updated / refreshed without having to open additional files (workbooks). Excel is programmed to only recognize the INDIRECT function when the referenced sheet is open. EXE which addresses the exact problem you are experiencing. The only thing that the summary file (workbook) is used for is to view and / or print.
may be quicker, however, if you have several links in your document because this method enables you to see a list of all links.
On the other hand, you can also press Ctrl A to select everything in your document and then press F9 .