How to Close Workbook Without Saving in Excel VBA

When working with Excel workbooks, there are more than a number of times when you open a file just to view or make temporary calculations. In some cases, saving the changes is not even necessary; in addition, saving can overwrite your data.

With Excel VBA, you can close the workbook without saving the recent changes and avoid the save confirmation prompt. In this article, we will guide you through three different VBA approaches to close the workbook without saving. It includes marking the workbook as saved, turning off the save prompts, and using the SaveChanges argument.

Sub CloseWithoutSaving()
    ThisWorkbook.Saved = True
    ThisWorkbook.Close
End Sub

What Does It Mean by Closing Workbook Without Saving?

Closing an Excel workbook without saving with VBA means achieving two main goals—automate closing of an Excel workbook, and second, doing so without saving any changes made during the last session.

In the following dataset, we have real estate listings containing property details. Now we are going to close the workbook using VBA without saving any changes that we will make.

This method uses the Saved = True property in VBA. When you set the Saved property to True, Excel takes it as the file has already been saved. So, it closes the Excel workbook without any confirmation prompt. As a result, you will see no change has occurred in the workbook since the last save.

In the dataset, we have made some notable changes in column A and column B to show you how these changes are discarded after closing the file with Excel VBA.

Steps:

➤ Open your Excel file.
➤ Press  Alt  +  F11  to open the VBA editor.
➤ In the VBA editor, go to Insert >> Module.

➤ Insert the following VBA code into the Module window. You can just copy and paste it.

Sub CloseWithoutSaving()
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

➤ Click on the Run >> Run Sub/UserForm from the toolbar above or simply press  F5  to run the code.

➤ The Excel workbook will close without saving any changes
➤ When you reopen the file, your unsaved changes will be lost.

You can also use the DisplayAlerts property of the program to close your Excel workbook without saving. This method temporarily disables the alert messages of Excel. That means no save prompt will be shown. So, as a result, you will be able to close the workbook silently without saving any last changes.

Steps:

➤ Open the VBA editor by pressing   Alt  +  F11  .
➤ Go to the  Insert >> Module and paste the following VBA code.

Sub CloseBook()
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

➤ Run the code.
➤ Your workbook will be closed immediately without prompting you to save.

The method uses the SaveChanges argument in the VBA to close the workbooks without saving. When you specify SaveChanges as False in the Close method, Excel understands that you don’t want to keep any changes. So, it closes the workbook immediately without showing any prompt for saving. As a result, all your unsaved changes disappear, and the workbook closes.

You can use the SaveChanges argument in different scopes based on which workbook you want to close.

Closing the Active Workbook Without Saving

This VBA macro closes the workbook that is currently active in your Excel. You can use the VBA code when you are working in the target workbook and want to close it directly.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Go to Insert >> Module
➤ Copy and paste the below VBA code.

Sub CloseActiveWorkbook()
ActiveWorkbook.Close SaveChanges:=False
End Sub

➤ Run the macro.
➤ Your currently active workbook will be closed without saving any changes, and you will be asked for confirmation.

Closing a Specific Excel File Without Saving

The following macro closes a specific Excel workbook by its name without saving any recent changes. If you have multiple workbooks open, you can target a specific one and close it by name. It will leave the other files open.

Steps:

➤ After opening the VBA editor, insert the following VBA code.

Sub CloseSpecificWorkbook()
Workbooks("Excel VBA Close Workbook Without Saving.xlsm").Close SaveChanges:=False
End Sub

➤ Run the VBA code.
➤ It will immediately close your specified workbook, and the unsaved changes will be discarded.

Closing All Excel Files Without Saving

The VBA macro will loop through all your open Excel workbooks and will close each of them in one go. It is helpful when you are done working with each workbook and want to close them without saving any changes.

Steps:

➤ Insert the below VBA code into the VBA editor.

Sub CloseAllWorkbooks()
Application.DisplayAlerts = False
Dim wb As Workbook
For Each wb In Workbooks
wb.Close SaveChanges:=False
Next wb
Application.DisplayAlerts = True
End Sub

➤ Run the code.
➤ All your open workbooks will be closed immediately, and any changes in any workbook among them will not be saved.

Frequently Asked Questions

How to retrieve a closed Excel without saving?

➤ To retrieve the closed Excel file without saving,
➤ Open MS Excel.
➤ Go to File >> Open >> Recover Unsaved Workbooks at the bottom of the list.
➤ Look for your file in the unsaved workbooks. Click on the file to open it if you see it.
➤ If you can’t see the file, then the file may not be possible to recover the file.

How to close an Excel File automatically?

➤ Open the VBA editor by pressing  Alt  +  F11  .
➤ Go to Insert >> Module.
➤ Insert the following VBA code.

Sub AutoCloseWorkbook_Save()
    ThisWorkbook.Close SaveChanges:=True
End Sub

➤ Run the code.

How to close an Excel File without closing Excel?

➤ Open the VBA editor.
➤ Insert the following code.

Sub CloseWorkbookOnly()
    ThisWorkbook.Close SaveChanges:=True 'or False if you don’t want to save
End Sub

➤ Run the code. It will close the Excel file without closing Excel.

Wrapping Up

In this quick tutorial, we have learnt how to close a workbook without saving using Excel VBA. Each of the methods offers you a faster and easier way to automate closing your workbook and not saving any recent changes. Feel free to download the sample Excel workbook and check out how exactly it works.