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.

Dataset: excel vba save and close workbook


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.

VBA Module: Save and Close Active Workbook by Using Excel VBA

  • 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.

Code Run: Save and Close Active Workbook by Using Excel VBA

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.

Read More: Excel VBA: Close Workbook Without Saving


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.

Excel VBA to Save and Close Specific Workbook

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

Save and Close Specific Workbook in Specific Folder

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.

Read More: Excel VBA: Check If Workbook Is Open and Close It


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).

Inserting Button to Save and Close Workbook in Excel

  • 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.

Final Output of Inserting Button to Save and Close Workbook in Excel

Read More: How to Close Workbook at Specific Time Using Excel VBA


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

excel vba save and close workbook

VBA Code Breakdown

  • We call our Sub Procedure CloseAndSaveOpenWorkbooks.
  • We use a For Next Loop to cycle through all Workbooks.
  • Using the Save method, we save our files.
  • We close all the Workbooks except our current Workbook.
  • We use the Quit property to close the original Workbook.
  • Execute the code.

Last Step of Save and Close All Open Workbook Applying Excel VBA


Download Practice Workbook


Related Article

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo