Neale Blackwood saves some time and files

Q. I work with a model that has many files and links between the files. When I make structural changes that affect links I have to go through and save all the open files to make sure the changes are kept. Is there a way to save all the open files in one step?

A. Microsoft Excel's 'Autosave' option can do this automatically every so many minutes. When working with a large model, 'Autosave' can be annoying and inconvenient. The macro below will save all the open Excel files that are not new. If you have a new file (for example, Book1) open that hasn't been saved, it will not be saved by this macro. The 'If statement' in the code ignores all new files.

Sub SaveAll()
Dim wb As Workbook
Dim wbActWb As Workbook
Application.ScreenUpdating = False
Set wbActWb = ActiveWorkbook
For Each wb In Workbooks
If wb.Path <> "" Then wb.Save
Next wb
wbActWb.Activate
Application.ScreenUpdating = True
End Sub

Neale Blackwood CPA is a senior business analyst with Access Analytic Solutions, which provides financial modelling and management reporting services, as well as advanced Excel training. You can email Neale at: nblackwood@accessanalytic.com.au