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

In this article, we will show you 5 methods of using Excel VBA to save and close Workbook. As time is very valuable, we can save a lot of time by skipping mundane tasks using Excel VBA. Moreover, 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


Download Practice Workbook


5 Examples to Save and Close Workbook Using VBA in Excel

1. Save and Close Active Workbook by Using Excel VBA

For the first method, we will save and close the Active Workbook using VBA Macro. We will bring up the VBA Module window, type our code and then execute the code to achieve our goal. Without further ado, let us jump into the steps by steps procedure.

Steps:

Before, typing our code we need to bring up the VBA Module. To do that –

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

  • Secondly, from Insert >>> select Module.

Here, we will type our code.

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

  • Thirdly, type the following code inside the Module.
Sub Save_and_Close_Active_Workbook()
ActiveWorkbook.Close SaveChanges:=True
End Sub

VBA Code Breakdown

  • First, we are calling our Sub Procedure Save_and_Close_Active_Workbook.
  • Then, we are referring to our current Workbook as ActiveWorkbook.
  • After that, using the Close method we are closing our file.
  • Finally, we have set  SaveChanges to True, which will save our Workbook upon closing.

Now, we will execute our code.

  • First, Save this Module.
  • Secondly, click inside our code.
  • Finally, 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: How to Save a Macro for All Workbooks in Excel (with Easy Steps)


2. Excel VBA to Save and Close Specific Workbook

For the second method, we will use save and close a specific Workbook using another VBA code. 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:

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

VBA Code Breakdown

  • First, we are calling our Sub Procedure Save_and_Close_Specific_Workbook.
  • Then, we are referring to our first Workbook inside the Workbooks object.
  • After that, using the Close method we are closing our file.
  • Finally, we have set  SaveChanges to True, which will save our Workbook upon closing.

After this, we will see that the first Workbook is closed and only the second Workbook is open. Therefore, we have shown you yet another method of saving and closing a Workbook.

Read More: Excel VBA: Save Sheet as New Workbook without Opening


3. Save and Close Specific Workbook in Specific Folder

For the third method, we are going to save and close a specific Workbook in a folder using Excel VBA.

Steps:

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

  • First, we are calling our Sub Procedure Save_and_Close_Workbook_in_Specific_Folder.
  • After that, using the SaveAs method we are saving our file in a specific location.
  • Then, we keep the filename the same as the original Workbook.
  • Finally, we close our Workbook.

After this, it will save our Workbook inside our defined folder location and close it. Thus, we have shown you another method of saving and closing Workbook in a specific folder using VBA.

Read More: Excel VBA Macro to Save PDF in Specific Folder (7 Ideal Examples)


Similar Readings


4. Inserting Button to Save and Close Workbook in Excel

For the fourth method, we are going to create a VBA button to close and save Workbook in Excel.

Steps:

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

VBA Code Breakdown

  • First, we are calling our Sub Procedure Button_Click_Save_and_Close_Workbook.
  • After that, using the Save method we are saving our Workbook
  • Finally, we close our Workbook using the Quit method.

Now, we will insert the VBA button here.

  • First, from the Developer tab >>> Insert >>> select Button (Form Control).

Inserting Button to Save and Close Workbook in Excel

  • Then, the mouse cursor will change and drag a box inside the Workbook.

After that, the Assign Macro dialog box will appear.

  • Then, select “Button_Click_Save_and_Close_Workbook”.
  • After that, press OK.

Then, we will see Button 1 in the Workbook.

  • Finally, click on the button.

This will save and close our Workbook.

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

Read More: VBA Code for Save Button in Excel (4 Variants)


5. Save and Close All Open Workbooks Applying Excel VBA

In this last method, we’re going to save and close all the opened Workbooks. 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:

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

  • First, we are calling our Sub Procedure CloseAndSaveOpenWorkbooks.
  • Then, we are using a For Next Loop to cycle through all Workbooks.
  • After that, using the Save method we are saving our files.
  • Then, we close all the Workbooks except our current Workbook.
  • Finally, we have closed the original Workbook using the Quit property.

Therefore, it will save and close the two Workbooks. We can see the date modified is the same for the two Workbooks. In conclusion, we have shown 5 different Excel VBA Macros to save and close Workbook.

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

Read More: How to Save Macro to Personal Macro Workbook?


Conclusion

We have shown you 5 quick-and-easy to understand Excel VBA to save and close Workbook. If you face any problems, feel free to comment below. Moreover, you can visit our site Exceldemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo