What am I missing in my code that doesn't “Close” the workbook (.xlsm) after saving it?

Question

What is the proper code to input in the module/button that will specifically prompt the user to "SaveAs", "Close" the (.xlsm) file, and open the same "New" sheet again?

I want to create an excel micro-enabled file .xlsm that will have a button called, "Save&New". The module/button will SaveAs, close the current file and open the same file again. For instance, when I'm filling out an audit and using the .xlsm sheet, I want to click the "Save&New" button for it to SaveAs, close it, and open the same file again but not the one I saved. Look at it has a template; only that I want the button to SaveAs, close the file, and open a "new" fresh file automatically.

Here is what I have so far:

In this image you see that I created the button called, "Save&New".

[enter image description here][1]

When I click on the "Save&New", it will "SaveAs" the .xlsm sheet.

[enter image description here][2]

This is where I need help. After saving the sheet, I want the workbook to "Close" the file.

And open the same sheet before to work on a "New" audit workbook form (.xlsm). (Not the sheet that was saved, but the sheet open before modifying it)

This is the code that I have inside the module/button called, "Save&New":

Sub Button7_Click()
    Application.Dialogs(xlDialogSaveAs).Show
    Dim oWB As Excel.Workbook
    For Each oWB In Application.Workbooks
        If oWB.Name = "_temp.xls" Then
            oWB.Close
            Exit For
        End If
    Next
    Set oWB = Nothing
    Workbooks.Open ("Communications Subdivisions System Audit.xlsm")
    ActiveWorkbooks.Close
End Sub

The code currently "SaveAs" and it opens a "New" sheet of the same one that was used before saving it. The problem is that it doesn't close the workbook that was saved before, although it does open the "New" sheet. Specifically, my question is:

What am I missing in my code that doesn't "Close" the workbook (.xlsm) after saving it and still opens a "New" sheet again?

rather than using ActiveWorkbooks.Close can you use Workbooks("YourFile").close — Apurv Pawar
Note that Application.Dialogs(xlDialogSaveAs).Show doesn't save any file. It returns a string that is the path/filename that the user chooses. You still need to do Workbook.SaveAs — Rich Holton
I don't know of anything called ActiveWorkbooks in the Excel object model. Only 1 workbook can ever be active at any given time. Perhaps you meant to use ActiveWorkbook? Suggestion: get the object reference returned by Workbooks.Open, and use that object reference instead of relying on activation behavior. Just... why would you close a workbook you literally just opened? — Mathieu Guindon
Also, specify Option Explicit at the top of every module, so that VBA will refuse to compile and run with typos. — Mathieu Guindon

Joel Coehoorn Joel Coehoorn · Accepted Answer · 2017-05-11T13:41:52

Unless there is more involved with the macro, you might be able to accomplish this entirely via a "Template" feature already built into Excel and save yourself a ton of work and trouble.

Get your "new" file the way you want it, choose "Save As", and then in the File Types section choose "Excel Template (*.xltx)". Note this will change the selected save folder, so put the save dialog back where you really want the file and save it.

Now, every time you open this file you get a new copy, and when you do a regular save it won't make changes to the original.

What am I missing in my code that doesn't “Close” the workbook (.xlsm) after saving it?

2 Answers