Excel VBA: Save and Close Workbook (5 Suitable Examples)

For this tutorial, we have taken a dataset consisting of 3 columns: “Name”, “Born”, and “Latest Work”. This dataset represents 6 people’s birth year and their latest film information.

Method 1 – Save and Close Active Workbook by Using Excel VBA

Steps:

From the Developer tab, select Visual Basic.

Alternatively, you can press ALT + F11 to do this too. The “Microsoft Visual Basic for Application” will appear after this.

From Insert, select Module.

Type the following code inside the Module.

Sub Save_and_Close_Active_Workbook()
ActiveWorkbook.Close SaveChanges:=True
End Sub

VBA Code Breakdown

We call our Sub Procedure Save_and_Close_Active_Workbook.
We refer to our current Workbook as ActiveWorkbook.
The file is closed using the Close method.
We have set SaveChanges to True, which will save our Workbook upon closing.

Now, we will execute our code.

Save this Module.
Click inside our code.
Press the Run button.

If we go to our Excel Application, we will see our Workbook is closed. Thus, we have successfully saved and closed the Workbook using Excel VBA.

Method 2 – Use Excel VBA to Save and Close Specific Workbook

Here, we have opened two Workbooks and we will save and close the first Workbook from the left side.

Steps:

Bring up the VBA Module.
Type this code inside that Module.

Sub Save_and_Close_Specific_Workbook()
Workbooks("Macro Save and Close.xlsm").Close SaveChanges:=True
End Sub

VBA Code Breakdown

We call our Sub Procedure Save_and_Close_Specific_Workbook.
We refer to our first Workbook inside the Workbooks object.
We use the Close method we are closing our file.
We have set SaveChanges to True, which will save our Workbook upon closing.

Execute the code.

Method 3 – Save and Close a Specific Workbook in a Specific Folder

Steps:

Bring up the VBA Module.
Type this code inside that Module.

Sub Save_and_Close_Workbook_in_Specific_Folder()
Workbooks("Macro Save and Close.xlsm").SaveAs _
Filename:="C:\Users\Rafi\OneDrive\Desktop\Exceldemy\Macro Save and Close.xlsm"
Workbooks("Macro Save and Close.xlsm").Close
End Sub

VBA Code Breakdown

We call Sub Procedure Save_and_Close_Workbook_in_Specific_Folder.
We save our file in a specific location using the SaveAs method
We keep the filename the same as the original Workbook.
We close our Workbook.

Execute the code.

Method 4 – Insert a Button to Save and Close Workbook in Excel

Steps:

Open the VBA Module.
Type this code inside that Module.

Sub Button_Click_Save_and_Close_Workbook()
ThisWorkbook.Save
Application.Quit
End Sub

VBA Code Breakdown

We call the Sub Procedure Button_Click_Save_and_Close_Workbook.
Using the Save method we save our Workbook
We close our Workbook using the Quit method.

Now, we will insert the VBA button here.

From the Developer tab, select Insert
Choose Button (Form Control).

Use the mouse to drag a box inside the Workbook.

The Assign Macro dialog box will appear.

Select “Button_Click_Save_and_Close_Workbook”.
Press OK.

You should see Button 1 in the Workbook.

Click on the button to save and close the workbook.

Method 5 – Save and Close All Open Workbooks Applying Excel VBA

This time, we have the same two Workbooks as in method 3, however, this time, we’ll save and close both Workbooks. Here, we will use the For Next Loop to go through our Workbooks.

Steps:

Bring up the VBA Module.
Type this code inside that Module.

Sub CloseAndSaveOpenWorkbooks()
    Dim z As Workbook
    With Application
        For Each z In Workbooks
            With z
                If Not z.ReadOnly Then
                    .Save
                End If
                If .Name <> ThisWorkbook.Name Then
                    .Close
                End If
            End With
        Next z
        .Quit
    End With
End Sub