Macro to save all open Excel files

Time saver

If you have a number of linked Excel files and you make a structural change (insert rows or columns) you need to save all the open files to ensure that the links are updated and retained. This can be tedious as there is no Save All option in Excel. Word has a Save All option, but not Excel.

A macro can fill this gap in Excel’s functionality.

The macro below will save all the open Excel files except any that are read only or any that haven’t been already saved eg new files that have just been created and not saved.

 Sub SaveAllOpenFiles()
' saves all open Excel files
' excludes
'   read only +
'   previously unsaved files
 
Dim wb As Workbook
 
'go through all open workbooks
For Each wb In Workbooks
 
  'only save those that
  'aren't read only +
  'those not previously saved (new workbooks)
  '.Path is the folder path of the file
  If Not wb.ReadOnly And Len(wb.Path) <> 0 Then
 
    wb.Save
 
  End If
 
Next wb
 
'clears the object variable from memory
Set wb = Nothing
 
End Sub

The macro goes through all the open workbooks checks their status and then saves those that meet the criteria.

To see instructions on how to install and use macros see this blog post.

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.