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 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 button (module) called, "Save&New":

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 to "Close" the workbook (.xlsm) after saving it and still opens a "New" sheet again?

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") ActiveWorkbooks.Close 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.