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.

Recycle Bin Outlook 2016 EPSON Scan Java Web Start Mozilla PowerPoint Print CD 2016 Firefox Hyper-v Acrobat Epson XP-830 Manager Reader DC User's Guide Virt DocN Google Cloud pri... Word 2016 Vivado USB Backup 2016.2 Excel 2016 do HLS Quartus ll 7.1 2016.2 Web Edition Access 2016 WhoC hed Reg Backups OneNote Multi WinTrilogi 3.5 SIM Install and J... 2016 O Type here to search 7:22 PM 5/9/2017

Explanation / Answer

Hi,

Please try below code. Its works-

Private Sub CommandButton1_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
.Quit 'Quit Excel
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.