Close Excel Workbook using VBA Macros

Add to Favorites

How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard any changes.

Sections:

Selecting Which Workbook to Close

Close Workbook While Saving Changes

Close Workbook Without Saving Changes

Let the User Decide to Save Changes or Not

Notes

Selecting Which Workbook to Close

First, we need to tell the macro to choose the current workbook to close or another workbook to close.

Current Workbook

We use this piece of code to close the current or currently active workbook and close that.

ActiveWorkbook.Close

Other Workbook

We use this piece of code to close any specific open workbook.

Workbooks("test.xlsx").Close

Replace test.xlsx with the name of the file that you want to close.

Close Workbook While Saving Changes

To have Excel automatically save any changes for the workbook that you want to close, just put True behind the close workbook code from above like this:

ActiveWorkbook.Close True

or, to close a specific file like this:

Workbooks("test.xlsx").Close True

Close Workbook Without Saving Changes

To have an Excel window close WITHOUT saving any changes, just put False behind the close workbook code from above like this:

ActiveWorkbook.Close False

or, to close a specific file like this:

Workbooks("test.xlsx").Close False

Let the User Decide to Save Changes or Not

You use the basic code from the first section and don't include a True or False after it and a dialog box will open asking if you want to save the file or not; it looks like this:

Close the currently active or visible workbook:

ActiveWorkbook.Close

Close a specific workbook:

Workbooks("test.xlsx").Close

Notes

You may run into issues with messages popping up depending on your implementation of this code and your setup and it can help to turn off ScreenUpdating for Excel. Make sure to turn it back on when you are finished though.

If Application.DisplayAlerts is set to False before you close the workbook, you won't see a popup asking if you want to save it or not before closing it. If this is the case, you may lose data if you wanted to save the file before closing it, so test your code on a sample workbook first.

Download the sample files for this tutorial to test everything out.


Downloadable Files: Excel File 1, Excel File 2

Similar Content on TeachExcel
Open Excel Workbook Using VBA Macros
Tutorial: Simple way to open an Excel workbook using VBA and macros. Syntax Workbooks.Open ("File...
Macro to get Data from Another Workbook in Excel
Tutorial: Macro to get data from a workbook, closed or open, over a network or locally on your comp...
Get User Submitted Data from a Prompt in Excel using VBA Macros
Tutorial: How to prompt a user for their input in Excel. There is a simple way to do this using VBA ...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Join a Date and Time into a Single Cell using VBA Macros in Excel
Tutorial: In order to combine a cell that has a date with a cell that has a time, using a Macro and ...
Excel Prank - Prevent Closing a Workbook
Tutorial: How to prevent a user from closing an Excel workbook. The user won't know what to do! Th...