An Excel VBA Macro to Save a Workbook Automatically When Closing

Whenever you try to close an Excel file, Excel application ask you whether you want to save the changes or not.

But for some applications we need to automatically save the changes when we close the file without showing above message. So in this post I will show you how to do that using VBA. First open an Excel file. If it is a .xlsx file then save it as .xlsm file. If this is a new excel workbook, first you need to save it manually as .xlsm file. Then go to the developer tab and click on the Visual Basic icon.

If you don't have developer tab this post will explain you how to show it.

How to show the Developer tab

Also you can use shortcut keys to open the VBA editor. Press Alt+F11

Then double click on ThisWorkbook module.

You will see two drop downs above the coding area. They have default values as (General) and (Declarations).

From the first drop down select “Workbook”. When you select that value second dropdown will be populated with new list. Then select “BeforeClose” from that second dropdown. New subroutine will be created like this.

Private Sub Workbook_BeforeClose(Cancel As Boolean) End Sub

Add this code between private sub and end sub

ActiveWorkbook.Save

so the final code should look like this.

Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Save End Sub

Now do some changes to the excel file and close it. Excel application will silently close the file without showing any message. Next time when you open the file you will notice that the changes are saved.