3 Tips to Save and Close All Open Excel Workbook Files + Macro

Bottom line: Learn a few shortcuts and tips to save and close all open Excel workbooks.

Skill level: Beginner

If you use Excel all day every day like I do, then it can be easy to have a lot of workbooks open at the same time.  I try to stay disciplined with this, but sometimes it's hard.  And at the end of the day, we just want to close all open workbooks and go home!  So this post contains a few tips for quickly closing all open files to help beat rush hour traffic. 🙂

Tip #1: Shift + Close Window Button

The quickest way to close all open workbooks is to hold the Shift key while pressing the Close Window button.  The Close Window button is the “x” in the top-right corner of the application window.

If all of the open workbooks have been saved, then they will all be closed.

If any of the open workbooks have NOT been saved, then you will be prompted to save the file.  The pop-up window will appear and you will have to press Save or Don't Save for each unsaved workbook.

The window also contains a Save All button.  You can press this to Save All the unsaved workbooks in one step.

Clicking the Cancel button will cancel the entire operation and the wordbooks that are still open will remain open.

But what if we don't want to save all or some of the open workbooks? We'll get to that in tip #3.

Tip #2: Add the Close All Button to the Quick Access Toolbar

We can also add the Close All button to the Quick Access Toolbar (QAT) to close all open workbooks.  This does the same thing as the Shift+Close Window shortcut.

The advantage here is that we can use a keyboard shortcut to press the button.  The QAT buttons can be accessed with a keyboard shortcut by pressing the Alt Key and the number assigned to the button.

To add the Close All button to the QAT, open the Excel Options window (File>Options) and follow the steps on the image below.

After pressing the Close All button we will be prompted with the same window as described in tip #1.

Bonus Tip: We can also use the keyboard shortcut Ctrl+W to close a single workbook.  I mentioned this tip in my post on 17 Excel shortcuts for 2017.

Tip #3: Close All Open Workbooks with a Macro

In this section I will share a few macros that can be used to close all open workbooks.  The macros give us a lot more flexibility when we want to control which workbooks are saved before closing.

You can download the Excel file that contains the macros here.

Macro to Close All Workbooks Without Saving

If we don't want to save any of the open files, then we can use the following macro.

Sub Close_All_Files_No_Save()
'Close all open workbooks and don't save

Dim wb As Workbook

  'Loop through each workbook
  For Each wb In Application.Workbooks
    
    'Prevent the workbook that contains the
    'code from being closed
    If wb.Name <> ThisWorkbook.Name Then
      
      'Close the workbook and don't save changes
      wb.Close SaveChanges:=False
    
    End If
  Next wb

End Sub

How does this macro work?

The macro above loops through all of the open workbooks on the computer using a For Next Loop.  It uses an If statement to check if the file in the loop (wb.Name) is NOT (<>) the file that contains the code ThisWorkbook.Name.

It then uses the Workbooks.Close method (MSDN Help Page) to close the workbook.  The Close method has an optional parameter to SaveChanges.  We can set this to True to save changes, and False to close without saving.

Note: This macro should only be run if you are certain you don't want to save changes to any of the open files.  There is no way to undo this.  You might also want to add a Yes/No Message Box prompt to warn the user before running this macro.

Macro to Save All Except New Unsaved Workbooks (Scratch Pads)

At the end of my workday I usually have a combination of some workbooks that I want to save, and some that I do NOT want to save.  I often open new workbooks to use the Excel files for quick calculations.  I call these “scratch pads”.  I don't necessarily want to save these, but I might want to save all other open files.

The scratch pad files have never been saved, so they don't have a file extension yet.  They are named: Book1, Book2, Book2, etc.  Notice in the screenshot that there is no file extension (.xlsx, .xlsm).

So we can add an If statement to our macro to see if a file extension exists.  If the file extension exists, then we will save the file.  If not, then we will close without saving.

Sub Save_and_Close_All_Files_Except_ScratchPads()
'Close all open workbooks except new unsaved files

Dim wb As Workbook

  'Loop through each workbook
  For Each wb In Application.Workbooks
    
    'Prevent the workbook that contains the
    'code from being closed
    If wb.Name <> ThisWorkbook.Name Then
      
      'Check if the file names has an extension
      If InStr(Right(wb.Name, 5), ".xls") > 0 Then
        wb.Close SaveChanges:=True
      Else
        'Do not save changes if it's a scratch pad.
        wb.Close SaveChanges:=False
      End If
    
    End If
  Next wb

End Sub

How does this macro work?

This macro is similar to the macro to close all workbooks. The difference is we use an If statement to test if the file has an extension.

The Right function is used to return the 5 characters at the end of the file name.  If wb is an existing file that has already been saved, then Right(wb.Name, 5) will return “.xlsx” or “.xlsm”.

The Instr function (MSDN Help Page) is used to find a string within a string.  It returns the starting character number of the string.  So, if Instr finds “.xls” within “.xlsx” or “.xlsm”, it will return 1.  Otherwise, it will return a 0.

The If statement checks if the return value from Instr is greater than 0, and saves changes while closing the workbook.  Otherwise, changes are not saved for workbooks that do NOT contain an extension.

Macro to Save and Close All Workbook and Automatically Name Files

Sometimes we might want to save some of those new files (scratch pads), but don't have time to go through each file and save it with a name.  This is like hitting the Excel eject button when we don't want to lose any of our work.

The following macro will save and close ALL files, and automatically name the new files that have not been saved.  It puts all the new (scratch pad) files in a folder that you specify.

You can then go back to that folder when you have some free time to move and rename the files.

Sub Save_and_Close_All_Files()
'Close all open workbooks except new unsaved files

Dim wb As Workbook
Dim sPath As String

  'The path where the new unsaved files will be saved.
  'Change this to a folder on your computer.  End with a backslash \
  sPath = "C:\Users\username\Documents\Excel Campus\Scratch Pads\"

  'Loop through each workbook
  For Each wb In Application.Workbooks
    
    'Prevent the workbook that contains the
    'code from being closed
    If wb.Name <> ThisWorkbook.Name Then
      
      'Check if the file names has an extension
      If InStr(Right(wb.Name, 5), ".xls") > 0 Then
        wb.Close SaveChanges:=True
      Else
        'Save scratchpads in a folder
        wb.Close SaveChanges:=True, _
            Filename:=sPath & wb.Name & Format(Now, " yyyy-mm-dd-hhmm")
      End If
    
    End If
  Next wb

End Sub

How does this macro work?

This macro works similar to the other two above.  The only difference is that it saves the new files (scratch pads) to a folder on your computer.

To use this macro you will need to change the sPath variable to a folder on you computer.  Make sure to end the file path string with a back slash.

The Workbooks.Close method has another optional parameter (Filename) that allows us to specify a file name where we want to save the file, if the file has NOT been saved yet.

If we do not use the Filename parameter, then Excel will prompt us with the Save As menu for each file.  So, specifying the Filename bypasses this window and can save us a lot of time.

The value of the Filename parameter is concatenating the string for the full file path.  It is also amending the current date and time to the end of the file name using the Format and Now functions.  The Format function in VBA is similar to the TEXT function in Excel, and allows us to specify a number format.  The Now function returns the current date and time on the user's computer.

I recommend adding these macros to your Personal Macro Workbook so you can use them anytime.  Checkout my article and video series on the personal macro workbook to learn more.

Free Webinar on Getting Started with Macros & VBA

If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”.  It's running all this week, and it's absolutely free to register.

During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started.  I jump into Excel and the VB Editor and walk through how to write and run our first macro.  Even if you have been using VBA for awhile, I'm sure you will learn some new tips.

Click here to learn more and register for the webinar

What Are Your Tips for Savings & Closing Files?

I hope those tips and macros help you end the day a little quicker.  Do you have any other tips for closing files.  Please leave a comment below with any suggestions or questions.  Thank you!