How to close all workbooks in Excel with VBA

If you are working with multiple Excel files at once, you often find yourself dealing with warning messages when trying to close them. Although it is a good thing to be warned about saving to not lose changes in a workbook, it may become annoying quickly. Thanks to VBA, you can create a macro to close them all, preferably save as well. In this guide, we’re going to show you how to close all workbooks in Excel with VBA.

Preparation

You cannot close all workbooks if your code closes the workbook which includes the code, right? You need to store your code in a special file named Personal.xlsb. Personal.xlsb is a special macro file also known as Personal Macro Workbook. This file will be created automatically when you record a macro in it deliberately. Once created, this workbook will be opened in the background whenever you start Excel.

To check whether the file has been created correctly, open a blank workbook, and activate the View tab in the Ribbon. If the Unhide button is disabled, then your Personal.xlsb file has not been created.

You can easily create it by recording a macro.

1. Click Record Macro in the Developer tab or Status Bar. If the Developer tab is hidden, see How to display the Excel Developer tab for using advanced features.
2. Select Personal Macro Workbook from the Store macro in list
3. Click the OK button to start recording a macro.
4. If you do not want to record any action, you can click the Stop Macro button right away.

Once the macro recording stops, Excel creates the Personal.xlsb file. You can either use the Unhide command to make it visible or open the VBA window and access it through there.

Write your macros in Personal.xlsb and save the file in the VBA window.

Macro

All you need is to loop through each workbook and execute Close method for the active workbook. A simple For Each loop to iterate all workbook objects in a parent Workbooks object is enough.

You can determine if the workbooks are saved or not by populating the Close method's SaveChanges argument. The default value of this argument is False which indicates no saving. Set it as True to save changes in your workbooks.

Do not forget to save Personal.xlsb file by the Save button or command in the VBA window.

Close all workbooks without saving changes

Sub CloseAllWorkbooks()
    'Define a workbook variable
    Dim wb As workbook
    'Loop through all workbooks and close them
    For Each wb In Workbooks
        wb.Close
    Next wb
End Sub

Close all workbooks without saving changes

Sub CloseAllWorkbooksAndSave()
    'Define a workbook variable
    Dim wb As workbook
    'Loop through all workbooks, save and close them
    For Each wb In Workbooks
        wb.Close SaveChanges:=True
    Next wb
End Sub