What is the proper code to input in the module/button that will specifically pro

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:

I created the button called, "Save&New".

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

This is where I need help. After saving the sheet, I want the workbook to "Close" the saved file, but not the file named, "Communications Systems Audit.xslm" file. Has you can see the file name, "Audit1" stayed open while the other one closed. I want to this the other way around.

This is the code that I have inside the button (module) 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.xlsm" Then
oWB.Close
Exit For
End If
Next
Set oWB = Nothing
Workbooks.Open ("Communications Subdivisions System Audit.xlsm")
'Workbooks.Close
Dim Wkb As Workbook

With Application
.ScreenUpdating = False

' Loop through the workbooks collection
For Each Wkb In Workbooks

With Wkb
If Not Wkb.ReadOnly Then

.Save

End If
If .Name <> ThisWorkbook.Name Then

.Close

End If

End With

Next Wkb

.ScreenUpdating = True
.DisplayAlerts = False 'set to true if want to be asked before saving
.EnableEvents = False 'block events from both workbooks

End With
End Sub

The code currently "SaveAs", stays on the saved sheet and closes the original audit sheet. The problem is that its closing the original first sheet. I want to do it the other way around.

How do I rearrange my code for me to "Save As" the file, then "Close" the saved file, and open "Communications Subdivisions Audit.xlsm" file again. IMPORTANT: Please don't suggest to make a Template file because I'am instructed to create this module (button) that will "Save As", "Close" the file, and then open a "New" sheet.

System Audit Forl Insert Page Layout Formulas Data Review view Developer Team Tell me what wou want to do File Home cut wrap Text Normal Bad Calib E Copy BI u A E EE E Merge & C E Conditional Format as Good Paste Neutral Format Painte Formatting Table A4 Mio Inspector 3 /28/2017 Site Name Address N/A N/A Save&Nev; a Type here to search insert Delete Form omments 2 AutoSum Clear. Soft nd & Filter Seloct 5/9/20 Share

Explanation / Answer

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

With Application
.ScreenUpdating = False

' Loop through the workbooks collection
For Each Wkb In Workbooks

With Wkb
If Not Wkb.ReadOnly Then

.Save

End If
If .Name <> ThisWorkbook.Name Then

.Close

End If

End With

Next Wkb

.ScreenUpdating = True
.DisplayAlerts = False 'set to true if want to be asked before saving
.EnableEvents = False 'block events from both workbooks

End With
End Sub

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.