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

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

3 Tips to Save and Close All Files2

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.

Close All Excel Workooks Shortcut Shift Key Close Button

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.

Excel Warning Want to Save Changes Before Closing File Window Save All

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.

Excel Close All Button Quick Access Toolbar

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.

QAT Keyboard Shortcut to Close All Excel Files

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

Add Close All But to Quick Access Toolbar in Excel Options

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.

Press Ctrl W to Close Last Excel Workbook and Leave Application Open

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.

Save And Close All Files Macros.xlsm (15.8 KB)

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.

Macro to Save and Close All Files Except New Unsaved 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.

Save and Close All Files and Auto Name New Files

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.

Unsaved Files Save to a Folder and Automatically Named

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.

Macros and VBA Webinar Banner - Join Me - 550x200

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 12 comments
powerful love spells - June 21, 2017

What’s up,I read your new stuff named “3 Tips to Save and Close All Open Excel Workbook Files VBA Macro” regularly.Your writing style is witty, keep up the good work! And you can look our website about powerful love spells.

Reply
Roy - June 18, 2017

Every time I have doubt about spending $$$ on one of your courses, I find one of your very helpful blog posts that removes my doubts. Not to mention the post usually provides a free macro too. Thank you for these time-saving/stress-reliving posts & macros!

Now off to buy your course on “Ultimate Lookup Formulas”.

Reply
Florence Lorenzo - June 16, 2017

Great tip Jon, thanks.

Reply
John MacDougall - May 10, 2017

Nice, I didn’t know about Shift + close.

You can also right click on the Excel icon from the Windows task bar and select close all windows!

Reply
Kotini Patra - May 10, 2017

Nice Tips

Reply
Linda - May 9, 2017

These are great tips! Always wanted to be able to close all excel files down all at once. I will definitely try the last option. Great job Jon!

Reply
ryan - May 9, 2017

I have used the last vba code of save the unwanted excel in a directory.
However, when I run the code, there is no files been saved after I checked the desired location I altered.

I already follow the steps.

Reply
    Jon Acampora - May 9, 2017

    Hi Ryan,
    The macro will only save unsaved files to that location. If you have unsaved files open, then the issue is likely caused by an error in the sPath variable. You can copy and paste that file path to the address bar in Windows Explorer to make sure it is valid. Also, make sure it ends with a back slash. I hope that helps.

    Reply
Surya - May 9, 2017

I have one doubt. Please clarify. Macro’s are working only one sheet / workbook only. How to utilize macro for entire excel sheets in the same computer.

Reply
Peter Buyze - May 9, 2017

Hey Jon, shared this on G+: https://plus.google.com/+PeterBuyze/posts/hjAaUPt4E9P.

Reply
Phil - May 9, 2017

I always Ctrl-Tab through all my open sheets, just to have a quick peek. Then I usually decide to hit the Save All button upon closing Excel.

Reply

Leave a Reply: