The way to create a VBA process that closes all open workbooks in Excel

This easy process can prevent numerous time while you’re completed along with your work. Susan Harkins tells you ways.

A few of us work with a number of Microsoft Excel workbooks open on the similar time. Both we open them and work some time and transfer on to the subsequent, or they’ve a hyperlink or connection that requires that all of them be open on the similar time. No matter how you find yourself with numerous open workbooks, closing all of them manually, one after the other, will be tedious.

The excellent news is that you should use a VBA process to close all of them as a substitute. On this article, I’ll present you a easy process that saves every workbook, if obligatory, earlier than closing it after which strikes on to the subsequent workbook, ultimately saving and shutting all of them.

SEE: 83 Excel suggestions each consumer ought to grasp (TechRepublic)

I’m utilizing Microsoft 365 on a Home windows 10 64-bit system. (I like to recommend that you just wait to improve to Home windows 11 till all of the kinks have been labored out.) You may obtain the .bas file, which comprises the process, and import the file into your Private.xlsb workbook. Excel On-line doesn’t assist VBA.

The place the process goes

The very first thing to get out of the best way is that this: You may’t retailer this process in simply any workbook. If it’s the final workbook open, the process will shut it first and cease. That is the kind of process that belongs in your private workbook. This workbook, named Private.xlsb opens each time you open Excel and is hidden by default. All procedures in Private.xlsb can be found to all open workbooks. It’s a library of kinds.

Open the Visible Fundamental Editor (VBE) by urgent Alt + F11. If the Mission Window to the left isn’t seen, press Ctrl + R or select Mission Explorer from the View menu. If Private.xlsb is within the Mission Explorer, increase it and double-click Module1 to open its code window.

If Private.xlsb isn’t within the Mission Window, you have to document a brief process as follows:

1. 1. Return to the worksheet and click on the Developer tab.
   2. Within the Macros group, click on Report Macro.
   3. Within the ensuing dialog, you solely want to decide on the place the process can be saved. From the Retailer Macro In dropdown, select Private Macro Workbook (Determine A).
   4. Click on OK
   5. Click on Cease Recording within the Macros group on the Developer tab.

   Return to the VBE and also you’ll discover Private.xlsb within the Mission Window, as proven in Determine B. Double-click Module1 to open its code sheet and delete the process you simply created. You don’t want this process; its solely goal was to unhide Private.xlsb.

   Determine A

   

   Determine B

   

   With an open code sheet for Private.xlsb, you’re prepared so as to add the code proven in Itemizing A. It actually is so simple as it appears (Determine C). In case you are utilizing a ribbon model, make sure you save the workbook as a macro-enabled file. Should you’re working within the menu model, you possibly can skip this step.

   Itemizing A

   Sub CloseWorkbooks()

   'Save and shut all open workbooks.

   Dim wb As Workbook

   'Turns off display screen.

   Utility.ScreenUpdating = False

   'Cycles by open workbooks and closes them.

   For Every wb In Workbooks

   Debug.Print wb.Title

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

   wb.Shut SaveChanges:=True

   Finish If

   Subsequent wb

   'Activates display screen.

   Utility.ScreenUpdating = True

   'Closes Excel.

   Utility.Give up

   Finish Sub

   Determine C

   

   The process is within the downloadable .bas information, which you’ll import into the Private.xlsb file’s Module 1. Should you enter the code manually, don’t paste from this net web page. As a substitute, copy the code right into a textual content editor after which paste that code into the Private.xlsb module. Doing so will take away any phantom net characters which may in any other case trigger errors.

   After you have the process in Private.xlsb, you possibly can run it from any workbook.

   In regards to the process

   This process first declares a Workbook object variable as wb and turns off display screen updating so that you received’t truly see information closing. The For Every assertion cycles by all of the open workbook objects and closes them, utilizing the =True worth to avoid wasting every workbook earlier than closing, except the present workbook is Private.xlsb. In that case, VBA skips this step and continues with the subsequent workbook. The code should skip the Private.xlsb workbook as a result of if it closes, it additionally stops operating, maybe leaving workbooks open. As soon as all of the workbooks are closed, the Utility.Give up assertion closes Excel. If you wish to go away Excel open, you possibly can remark out this assertion.

   If Excel is open, shut it. Open a couple of Excel workbooks and from any open workbook, click on the Developer tab after which click on Macros within the Macros group. Within the ensuing dialog, proven in Determine D, choose the CloseWorkbooks process and click on Run. The process saves and closes every open workbook after which quits Excel.

   Determine D

   

   Almost certainly, you received’t wish to work by all these steps to run this macro. I like to recommend that you just add it to the Fast Entry Toolbar (QAT) or a customized group. Should you need assistance with that, learn The way to add Workplace macros to the QAT toolbar for fast entry.