Close an Excel Workbook with VBA Without Losing Work

VBA & macros · Updated November 20, 2026

To close an Excel workbook with VBA, call its Close method and decide whether to save changes: choose True to save, False to discard, or omit the setting if you want Excel to ask. First identify the workbook you mean; using the wrong workbook reference can close a different file or lose unsaved edits.

What You’ll Need

  • Desktop Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros. Excel for Mac supports VBA, though the editor shortcuts and some interface details differ.
  • A workbook open in Excel and a clear choice about whether its changes should be saved.
  • A macro-enabled workbook saved as .xlsm if you want to keep the macro in that file. Save a separate copy of the workbook before running any macro: macros cannot be undone with Ctrl+Z.
  • Permission to run macros. Only enable macros from files and sources you trust; do not turn on all macros just to run an unfamiliar file.

Step-by-Step

  1. Choose the workbook to close. Decide whether the macro should close the workbook that contains the code, a workbook identified by its file name, or the workbook currently active in Excel. The examples below use these different references. Prefer a specific reference when the consequences of closing the wrong file would matter.
  2. Open the Visual Basic Editor. On Windows, press Alt+F11, or choose Developer > Visual Basic. If Developer is not shown, right-click the ribbon, choose Customize the Ribbon, and select the Developer check box under Main Tabs. On Mac, use Developer > Visual Basic; the keyboard shortcut may be Option+F11, sometimes with the Fn key depending on the keyboard. Excel for the web has no VBA editor.
  3. Add a standard module. In the editor, select the target workbook in Project Explorer. Choose Insert > Module. If Project Explorer is hidden, use View > Project Explorer. A standard module is a straightforward place for a macro you will run manually. Keep a saved backup before testing any code that closes or changes a workbook.
  4. Write a macro to close the workbook containing the code. Paste this example into the module if that is the file you intend to close. The ThisWorkbook reference means the workbook where this VBA code is stored, not necessarily whichever workbook is currently selected.
Sub CloseThisWorkbookAndSave()
    Dim wb As Workbook

    ' ThisWorkbook is the file that contains this macro.
    Set wb = ThisWorkbook
    wb.Close SaveChanges:=True
End Sub

With SaveChanges set to True, Excel saves changes to that workbook as part of closing it. If it is a new workbook that has never been saved, Excel may need a file name and location. If you want to discard edits instead, use False, but only when that is intentional. If you leave out SaveChanges, Excel can prompt the user about unsaved changes.

  1. Use the file name when closing a particular open workbook. Replace MonthlyReport.xlsx with the exact workbook name shown in Excel, including its extension. This example checks whether the workbook is open before trying to close it. The False setting closes it without saving its changes.
Sub CloseNamedWorkbookWithoutSaving()
    Dim wb As Workbook

    ' Look for this exact workbook name among open workbooks.
    On Error Resume Next
    Set wb = Workbooks("MonthlyReport.xlsx")
    On Error GoTo 0

    If wb Is Nothing Then
        MsgBox "MonthlyReport.xlsx is not open."
        Exit Sub
    End If

    ' Use False only when discarding edits is intended.
    wb.Close SaveChanges:=False
End Sub

Workbooks(…) searches the workbooks currently open in this Excel instance, not a folder on disk. If two files have similar names, confirm the full name carefully. If the file name changes, update the name in the code. Using a full path is not the normal way to identify an already-open workbook through the Workbooks collection.

  1. Ask before deciding whether to save. For a macro used by other people, a Yes/No/Cancel prompt is safer than silently throwing away edits. Replace the workbook name with the intended open file. Yes saves, No discards, and Cancel leaves the workbook open.
Sub AskBeforeClosingWorkbook()
    Dim wb As Workbook
    Dim answer As VbMsgBoxResult

    ' Identify the specific workbook to close.
    Set wb = Workbooks("MonthlyReport.xlsx")
    answer = MsgBox("Save changes before closing?", _
                    vbYesNoCancel + vbQuestion, "Close workbook")

    Select Case answer
        Case vbYes
            wb.Close SaveChanges:=True
        Case vbNo
            wb.Close SaveChanges:=False
        Case vbCancel
            ' Leave the workbook open.
            Exit Sub
    End Select
End Sub

This prompt example expects the named workbook to be open. If it might not be open, add the same existence check used in the previous example before the MsgBox line. Also consider whether the prompt wording makes it unmistakable which workbook is affected.

  1. Run and verify the macro. Return to Excel, select Developer > Macros, choose the macro name, and select Run. You can also press Alt+F8 on Windows to open the Macro dialog. On Mac, use Developer > Macros or the Mac keyboard equivalent available in your Excel setup. Check that the intended workbook closed and that any required changes were saved. If you need to retain the macro, store it in a different macro-enabled workbook or in Personal.xlsb; a macro cannot remain available in a workbook after that workbook has closed.

Closing a workbook is different from quitting Excel. A workbook’s Close method closes that workbook; Application.Quit exits Excel and may affect every open workbook. Do not use a quit command if your goal is only to close one file.

Common Mistakes

  • Using ActiveWorkbook without checking the selection. ActiveWorkbook means the workbook currently in front. A click, another macro, or an event can change which workbook is active. Use ThisWorkbook for the code’s own file or a named workbook reference for a specific target.
  • Choosing False when you meant to save. SaveChanges:=False discards edits without the usual save confirmation. If you are unsure, use a prompt or omit the setting so Excel can ask. Do not test discard behavior on the only copy of important data.
  • Confusing ThisWorkbook with ActiveWorkbook. ThisWorkbook is where the macro is stored; ActiveWorkbook is the currently active file. They may be the same, but often are not, especially when the macro is stored in Personal.xlsb or another controller workbook.
  • Using the wrong workbook name. The name must match the open workbook, including its extension. A typo or mismatch can produce an error or make the lookup fail. Handle the missing-workbook case before calling Close.
  • Trying to undo a macro. Ctrl+Z does not reverse VBA changes. Save a backup, test on a duplicate, and make the save/discard choice explicit before running a closing macro.

Tips

  • Prefer a named workbook or ThisWorkbook over ActiveWorkbook in automation. They communicate intent and reduce the chance of closing the wrong file.
  • Use SaveChanges:=True only when the code should save the workbook as it currently stands. For workbooks with required data validation or review steps, a user confirmation may be more appropriate.
  • If the macro should close several known workbooks, close them one at a time with an explicit save choice for each. Avoid looping over all open workbooks unless you really intend to close every file.
  • Store a reusable closing macro somewhere that stays open, such as a trusted macro-enabled utility workbook or Personal.xlsb. Be careful not to close the workbook that contains the only copy of the macro before it is run.
  • Keep the message in a confirmation prompt specific. If users have several reports open, identify the target workbook in the prompt so they can make an informed decision.

Troubleshooting

The macro says the workbook is not open. Check the name in Workbooks(…) against the title bar, including .xlsx, .xlsm, or another extension. Confirm the file is open in the same Excel instance where the macro runs. A workbook opened in a separate Excel process is not in the current instance’s Workbooks collection.

Excel reports a subscript out of range error. This commonly means the workbook name could not be found in the collection. Use the existence-check pattern in the named-workbook example, and check for spelling, spaces, and the extension. If you intended the workbook that stores the code, use ThisWorkbook instead of a typed name.

Excel still asks whether to save. Confirm that the macro reaches the Close line and that you supplied SaveChanges:=True or False. A prompt may also relate to another workbook being closed or to a separate Excel action; check which file the dialog names. If you omit the argument, a save prompt for unsaved changes is expected.

The file closes but the macro is no longer available. That can happen when the code is stored in the workbook that was closed. Put the macro in a different workbook that remains open or in Personal.xlsb, then run it again. If the macro itself is part of the target workbook, closing that workbook is expected to make its code unavailable afterward.

The macro does not run or Excel blocks it. Confirm the file is saved as .xlsm if it needs to contain VBA, and use Excel desktop rather than Excel for the web. Review the macro security notification and only enable content when the workbook is from a trusted source. Do not lower global security settings to run unknown code.

A workbook remains open after the macro runs. Make sure you are checking the same Excel instance and that the macro targets the intended workbook. If you use a prompt, choose Yes or No; Cancel deliberately leaves the workbook open. If the workbook is protected or has other application-specific restrictions, resolve the relevant dialog or restriction rather than adding an indiscriminate Excel quit command.

Frequently Asked Questions

How do I close the workbook that contains the VBA code?
Use ThisWorkbook as the workbook reference and call its Close method. Set SaveChanges to True, False, or leave it out according to whether you want to save, discard, or prompt.

How do I close the active workbook?
Use ActiveWorkbook as the reference, but verify which workbook is active immediately before closing. For a more dependable macro, use ThisWorkbook or refer to the intended workbook by name.

What is the difference between SaveChanges:=True and False?
True tells Excel to save the workbook while closing it. False tells Excel not to save its changes. Neither choice is an undo feature, so keep a backup before running a macro that may discard data.

Can I close a workbook without closing Excel?
Yes. Close the workbook object only. Do not use Application.Quit unless your intention is to exit Excel and deal with all other open workbooks too.

Can I run a VBA closing macro in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Open the workbook in the desktop version of Excel to run the macro.

Can Ctrl+Z restore a workbook closed by a macro?
No. Excel’s Undo command does not reverse VBA operations. If changes were discarded or overwritten, recovery depends on a saved backup, AutoRecover, or available file version history; make a copy before testing closing code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.