BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Close all Saved Workbooks in Excel Application

Imagine you're working on some important excel files and gradually your taskbar is full of open workbooks. Some have similar names (not same ofcourse) and now you want to close all the saved workbooks and want unsaved workbooks to remain open. This macro will definitely solve your purpose. This is a kind of macro, one could save in a personal workbook and assign it to a 'Shortcut key'.

If you are new to the VBA macros in MS Excel, follow these instructions:
  1. Create a new or blank Excel workbook.
  2. Press ALT+F11 to open the Visual Basic Editor in MS Excel.
  3. Go to Menu --> Select 'Insert' --> Select 'Module'.
  4. Copy and Paste any or all of the following macro in the new module sheet.

Sub CloseSavedFiles()
     'This subroutine will close all the saved workbooks.
     Dim myWorkBook As Workbook
     On Error Resume Next
     For Each myWorkBook In Application.Workbooks
          'IF WORKBOOK IS SAVED
          If myWorkBook.Saved = True Then
               'THEN CLOSE IT
               myWorkBook.Close
          End If
     Next
     On Error GoTo 0
End Sub

How to use? To run these macros, simply press F5 while placing the cursor inside the macro code.
OR From MS Excel window, press 'Alt+F8' to select the macro and then hit the 'Run' button.
References For more information about the properties used inside above macros, type the following text on a module sheet:
  • ThisWorkbook.Saved
Highlight the property about which you want more information, and then press F1.
Supported versions of MS Excel: APPLIES TO
  • Microsoft Office Excel 2010
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition