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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 thoughts on “Macro to save all open Excel files

  1. Sorry, the If statement is not displaying correctly. It should be

    If Not wb.ReadOnly And Len(wb.Path) > 0 Then

    I am working on getting it fixed in the programming display

  2. I just wanted to say thank you so much, I found two other sites with incorrect, non-working code for this task, but your code (once I made the change in the Comments) worked. Can’t wait to set this up on my work computer and then on my boss’s work computer!