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.