|
|
|
|
|
|
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
For Each Wkb In Workbooks
With Wkb
If Not Wkb.ReadOnly Then
.Save
End If
If .Name <> ThisWorkbook.Name Then
.Close
End If
End With
Next Wkb
.ScreenUpdating = True
.Quit
End With
End Sub
|
How to use:
|
- Open an Excel Workbook
- Copy the code
- Press Alt + F11 to open the Visual Basic Editor (VBE)
- Select INSERT > MODULE from the menubar
- Paste code into the right pane
- Press Alt+Q to return to Excel
- Save workbook before any other changes
|
Test the code:
|
- 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.
- To test accurately, open a few workbooks, and run the code >
- Go to TOOLS > MACRO > MACROS
- When the dialog appears, select {CloseAndSaveOpenWorkbooks}
- Press Run
|
Sample File:
|
Close-All.zip 11.01KB
|
Approved by mdmackillop
|
This entry has been viewed 236 times.
|
|