Macro to Close Workbooks

These VBA macros close every other open workbook in one go, either saving the changes or discarding them, so you can tidy up a cluttered Excel session quickly.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Close all other workbooks and save them

Sub CloseOthersSave()
    Dim i As Long
    For i = Workbooks.Count To 1 Step -1
        If Not Workbooks(i) Is ActiveWorkbook And Workbooks(i).Windows(1).Visible Then
            Workbooks(i).Close SaveChanges:=True
        End If
    Next i
End Sub

Windows(1).Visible skips hidden workbooks such as your Personal Macro Workbook, so your saved macros stay open.

Close all other workbooks without saving

Sub CloseOthersNoSave()
    Dim i As Long
    If MsgBox("Close the other workbooks without saving?", vbYesNo) = vbNo Then Exit Sub
    For i = Workbooks.Count To 1 Step -1
        If Not Workbooks(i) Is ActiveWorkbook And Workbooks(i).Windows(1).Visible Then
            Workbooks(i).Close SaveChanges:=False
        End If
    Next i
End Sub

It asks first, because unsaved changes are lost.

Save everything and quit Excel

Sub SaveAllAndQuit()
    Dim wb As Workbook
    For Each wb In Workbooks
        If wb.Path <> "" And Not wb.ReadOnly Then wb.Save
    Next wb
    Application.Quit
End Sub

New workbooks that have never been saved will still ask for a file name.

Without a macro

  1. Press Ctrl + W in each workbook to close it.
  2. Or hold Shift and click the X in the top-right corner. Older versions of Excel then close every workbook.

Things to know

  • The loops count down from the last workbook, so closing one doesn’t make the macro skip the next.
  • The workbook you’re in stays open.

Related macros