Excel

Save and Close All Open Workbooks

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Saves all the open workbooks, then closes them all. Does not save if workbook is marked as Read-Only. 

Discussion:

If you work with a lot of open workbooks, when you are finished you may want an easier way to save all of them and close them in one shot. This code will loop through all the open workbooks, saving all of the ones that are not Read-Only, then closing them, including the one the code is running from. 

Code:

instructions for use 

			
Option Explicit 
 
Sub CloseAndSaveOpenWorkbooks() 
    Dim Wkb As Workbook 
     
    With Application 
        .ScreenUpdating = False 
         
         '       Loop through the workbooks collection
        For Each Wkb In Workbooks 
             
            With Wkb 
                 
                 '               if the book is read-only
                 '               don't save but close
                If Not Wkb.ReadOnly Then 
                     
                    .Save 
                     
                End If 
                 
                 '               We save this workbook, but we don't close it
                 '               because we will quit Excel at the end,
                 '               Closing here leaves the app running, but no books
                If .Name <> ThisWorkbook.Name Then 
                     
                    .Close 
                     
                End If 
                 
            End With 
             
        Next Wkb 
         
         
        .ScreenUpdating = True 
        .Quit 'Quit Excel
    End With 
End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes
 

Test the code:

  1. The attachment has a folder with workbooks to open, one is Read-Only, but to test you can have multiple workbooks open or the one with the code.
  2. To test accurately, open a few workbooks, and run the code >
  3. Go to TOOLS > MACRO > MACROS
  4. When the dialog appears, select {CloseAndSaveOpenWorkbooks}
  5. Press Run
 

Sample File:

Close-All.zip 11.01KB 

Approved by mdmackillop


This entry has been viewed 236 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express