Macro 11: Close All Workbooks at Once
One of the more annoying things in Excel is closing many workbooks at once. For each workbook
you have opened, you need to activate the work, close it, and confirm save changes. There is no easy
way to close them all down at one time. This little macro takes care of that annoyance.
How it works
In this macro, the Workbooks collection loops through all the open workbooks. As the macro loops
through each workbook, it saves and closes them down.
Sub Macro11()
‘Step 1: Declare your variables
Dim wb As Workbook
‘Step 2: Loop through workbooks, save and close
For Each wb In Workbooks
wb.Close SaveChanges:=True
Next wb
End Sub
1. Step 1 declares an object variable that represents a Workbook object. This allows us to
enumerate through all the open workbooks, capturing their names as we go.
2. Step 2 simply loops through the open workbooks, saving and closing them. If you don’t
want to save them, change the SaveChanges argument from True to False.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, it doesn’t exist yet. You’ll have to record a
macro, using Personal Macro Workbook as the destination.
