![excel vba on close save a sheet excel vba on close save a sheet](https://d295c5dn8dhwru.cloudfront.net/wp-content/uploads/2019/06/23045519/Screen-Shot-2019-06-23-at-11.52.56.png)
ThisWorkbook.Save ThisWorkbook.Worksheets(sActiveSheetName).Select End If bolInProcess = False End If End If End Sub Private Sub Workbook_Open() Worksheets("MySheet").ActivateEnd Sub Viola!// '// Kill screen updating and the user will be none the wiser. We won't recurse to here, but after the Save, the // '// next line reselecting the active sheet we started with will execute. Cancel = True ThisWorkbook.Worksheets(ENABLE_CONTENT_NAME).Select '// Now call a fresh Save. Cancel the Save, select the sheet we want in the wb's Saved state. sActiveSheetName = ActiveSheet.Name If Not sActiveSheetName = ENABLE_CONTENT_NAME Then '//. Visible true to get to the second worksheet, but that doesnt work. If Not bolInProcess Then bolInProcess = True '// Check the activesheet's name, and if different that what we want. I have tried different methods to save, close and quit Excel without any success. If Not SaveAsUI Then '// Use a static flag to prevent recurse. In the Save As dialog box, please select a folder to save the file, name it as you need in the File name box, select Excel Macro-Enabled Workbook from the. Static sActiveSheetName As StringStatic bolInProcess As Boolean '// I am not sure how I'd handle SaveAs. Const ENABLE_CONTENT_NAME = "EnableContent" '// Static to retain values between calls.
EXCEL VBA ON CLOSE SAVE A SHEET CODE
If the copied worksheet held a worksheet code sheet in a VBA project, that is also carried into the new workbook. Regardless of that, I would include error checking to ensure that the //'// sheet still exists. The single worksheet retains the Name and CodeName properties of the source worksheet.
![excel vba on close save a sheet excel vba on close save a sheet](https://www.teachexcel.com/images/uploads/783c4bd3c224f7b31ae1fb3995a17d60.png)
Step 3: Run the macro and it will save the changes that you made and close the work book.
![excel vba on close save a sheet excel vba on close save a sheet](https://d13ot9o61jdzpp.cloudfront.net/images/VBA.Create.PDF.and.Email.It.Anim.gif)
Step 2: Make whatever changes you want to the worksheets / workbook. Sub SaveAndClose() ActiveWorkbook.Close SaveChanges:True End Sub. Option Explicit Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)'// PLease note, I would use the sheet's CodeName (less likely altered than the name on //'// the tab). Step 1: Copy paste this Save and Close VBA macro in to a new module. Lightly tested, so by all means, try this in a junk copy of your wb first. Rather than using the before close event, how about the before save event.