How to create a VBA procedure that closes all open workbooks in Excel

This simple procedure can save you a lot of time when you’re finished with your work. Susan Harkins tells you how.

Some of us work with several Microsoft Excel workbooks open at the same time. Either we open them and work a while and move on to the next, or they have a link or connection that requires that they all be open at the same time. Regardless of how you end up with lots of open workbooks, closing them all manually, one by one, can be tedious.

The good news is that you can use a VBA procedure to shut them all instead. In this article, I’ll show you a simple procedure that saves each workbook, if necessary, before closing it and then moves on to the next workbook, eventually saving and closing them all.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. (I recommend that you wait to upgrade to Windows 11 until all the kinks have been worked out.) You can download the .bas file, which contains the procedure, and import the file into your Personal.xlsb workbook. Excel Online doesn’t support VBA.

Where the procedure goes

The first thing to get out of the way is this: You can’t store this procedure in just any workbook. If it’s the last workbook open, the procedure will close it first and stop. This is the type of procedure that belongs in your personal workbook. This workbook, named Personal.xlsb opens every time you open Excel and is hidden by default. All procedures in Personal.xlsb are available to all open workbooks. It’s a library of sorts.

Open the Visual Basic Editor (VBE) by pressing Alt + F11. If the Project Window to the left isn’t visible, press Ctrl + R or choose Project Explorer from the View menu. If Personal.xlsb is in the Project Explorer, expand it and double-click Module1 to open its code window.

If Personal.xlsb isn’t in the Project Window, you need to record a short procedure as follows:

1. 1. Return to the worksheet and click the Developer tab.
   2. In the Macros group, click Record Macro.
   3. In the resulting dialog, you only need to choose where the procedure will be stored. From the Store Macro In dropdown, choose Personal Macro Workbook (Figure A).
   4. Click OK
   5. Click Stop Recording in the Macros group on the Developer tab.

   Return to the VBE and you’ll find Personal.xlsb in the Project Window, as shown in Figure B. Double-click Module1 to open its code sheet and delete the procedure you just created. You don’t need this procedure; its only purpose was to unhide Personal.xlsb.

   Figure A

   Figure B

   With an open code sheet for Personal.xlsb, you’re ready to add the code shown in Listing A. It really is as simple as it looks (Figure C). If you are using a ribbon version, be sure to save the workbook as a macro-enabled file. If you’re working in the menu version, you can skip this step.

   Listing A

   Sub CloseWorkbooks()

   'Save and close all open workbooks.

   Dim wb As Workbook

   'Turns off screen.

   Application.ScreenUpdating = False

   'Cycles through open workbooks and closes them.

   For Each wb In Workbooks

   Debug.Print wb.Name

   If wb.Name <> "PERSONAL.XLSB" Then

   wb.Close SaveChanges:=True

   End If

   Next wb

   'Turns on screen.

   Application.ScreenUpdating = True

   'Closes Excel.

   Application.Quit

   End Sub

   Figure C

   The procedure is in the downloadable .bas files, which you can import into the Personal.xlsb file’s Module 1. If you enter the code manually, don’t paste from this web page. Instead, copy the code into a text editor and then paste that code into the Personal.xlsb module. Doing so will remove any phantom web characters that might otherwise cause errors.

   Once you have the procedure in Personal.xlsb, you can run it from any workbook.

   About the procedure

   This procedure first declares a Workbook object variable as wb and turns off screen updating so you won’t actually see files closing. The For Each statement cycles through all the open workbook objects and closes them, using the =True value to save each workbook before closing, unless the current workbook is Personal.xlsb. In that case, VBA skips this step and continues with the next workbook. The code must skip the Personal.xlsb workbook because if it closes, it also stops running, perhaps leaving workbooks open. Once all the workbooks are closed, the Application.Quit statement closes Excel. If you want to leave Excel open, you can comment out this statement.

   If Excel is open, close it. Open a few Excel workbooks and from any open workbook, click the Developer tab and then click Macros in the Macros group. In the resulting dialog, shown in Figure D, select the CloseWorkbooks procedure and click Run. The procedure saves and closes each open workbook and then quits Excel.

   Figure D

   Most likely, you won’t want to work through all those steps to run this macro. I recommend that you add it to the Quick Access Toolbar (QAT) or a custom group. If you need help with that, read How to add Office macros to the QAT toolbar for quick access.