Executing on Save

turbo805

New Member
Joined
Oct 24, 2016
Messages
22
Hello,

Briefly I have a code that creates a separate non-macro version of my workbook and saves it with a different name.

Works well when used as a normal Macro.

However, now i'm trying to get it to execute when my workbook is saved (to keep from having to press a button to save the non-macro version).
This version does not work.

It gives me the error:
Run-time error '91':
Object variable or With block variable not set.

I set my variables at the very beginning and it works as a normal macro, so what gives?
In bold is where the error occurs.

Cheers!

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    
    Dim wb As Workbook, wb2 As Workbook
    Dim Path As String, WorkbookName As String, FName1 As String, FName2 As String


    WorkbookName = ActiveWorkbook.Name
    Path = "C:\Users\" & Environ("Username") & "\Documents\"
    Set wb = ThisWorkbook
    wb.Save
                                           
    FName1 = Path & "Copy of " & WorkbookName
    wb.SaveCopyAs (FName1)                            'save copy


    If InStr(WorkbookName, ".") > 0 Then
        WorkbookName = Left(WorkbookName, InStr(WorkbookName, ".") - 1)
    End If


    FName2 = Path & WorkbookName & " " & "DASHBOARD.xlsx"


    Set wb2 = Workbooks.Open(FName1)
    Application.DisplayAlerts = False
    [B]wb2.SaveAs FName2, xlOpenXMLWorkbook  [/B]            'use copy to save macro-free workbook
    wb2.Close
    VBA.Kill FName1                                   'delete copy
    Application.DisplayAlerts = True
    
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
.
Unless I misunderstand (let me know) you are wanting to save a MACRO ENABLED workbook as a NON-MACRO ENABLED workbook in the DOCUMENTS folder. Having that
saved workbook open after creation.

Code:
Option Explicit


Sub sveme()
Dim newFilePath As String
Dim newFileFullName As String
Application.DisplayAlerts = False
newFileFullName = "C:\Users\" & Environ("Username") & "\Documents\DASHBOARD.xlsx"


ActiveWorkbook.SaveAs Filename:=newFileFullName, FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False


Workbooks.Open Filename:=newFileFullName
Windows("DASHBOARD.xlsx").Activate
Application.DisplayAlerts = True


End Sub
 
Upvote 0
.
Unless I misunderstand (let me know) you are wanting to save a MACRO ENABLED workbook as a NON-MACRO ENABLED workbook in the DOCUMENTS folder. Having that
saved workbook open after creation.

Yes exactly. But I'd like the non-macro enabled workbooks name to be based off the macro enabled workbooks with DASHBOARD added at the end.

As well, I'm trying to execute this by pressing the "Save" icon in the GUI of excel.

Essentially i'm trying to have a non-macro enabled version of my macro enabled workbook upon pressing the "Save" icon with the same name but with DASHBOARD attached, while keeping my original file open.

I tried your code and it gave me an error timing me out, even though it executed the save properly (there was a DASHBOARD version in my documents folder).
 
Last edited:
Upvote 0
It gives me the error:
Run-time error '91':
Object variable or With block variable not set.

I set my variables at the very beginning and it works as a normal macro, so what gives?
In bold is where the error occurs.
The wb.Save and wb2.SaveAs are both triggering the Workbook_BeforeSave procedure. To see this, set a breakpoint on those lines, save the workbook and step through with the F8 key.

I don't think you need the wb.Save because the macro workbook is saved anyway when the event procedure exits (unless a Cancel = True statement is executed).

To fix the wb2.SaveAs error, try putting Application.EnableEvents = False before it and Application.EnableEvents = True after it.
 
Upvote 0
The wb.Save and wb2.SaveAs are both triggering the Workbook_BeforeSave procedure. To see this, set a breakpoint on those lines, save the workbook and step through with the F8 key.

I don't think you need the wb.Save because the macro workbook is saved anyway when the event procedure exits (unless a Cancel = True statement is executed).

To fix the wb2.SaveAs error, try putting Application.EnableEvents = False before it and Application.EnableEvents = True after it.

You are the man. This was driving me insane, thank you so much.
 
Upvote 0
Glad you got it working!

Here is another approach which doesn't require the temporary .xlsm copy.

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim Path As String
    Dim currentSheet As Worksheet
    Dim i As Long
    
    Path = "C:\Users\" & Environ("Username") & "\Documents\"
    
    With ThisWorkbook
        Set currentSheet = ActiveSheet
        .Worksheets(1).Select
        For i = 2 To .Worksheets.Count
            .Worksheets(i).Select Replace:=False
        Next
        .Worksheets.Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=Path & Left(.Name, InStrRev(.Name, ".") - 1) & " DASHBOARD.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        Application.DisplayAlerts = True
        ActiveWorkbook.Close False
        currentSheet.Select
    End With

End Sub
 
Upvote 0
Glad you got it working!

Here is another approach which doesn't require the temporary .xlsm copy.

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim Path As String
    Dim currentSheet As Worksheet
    Dim i As Long
    
    Path = "C:\Users\" & Environ("Username") & "\Documents\"
    
    With ThisWorkbook
        Set currentSheet = ActiveSheet
        .Worksheets(1).Select
        For i = 2 To .Worksheets.Count
            .Worksheets(i).Select Replace:=False
        Next
        .Worksheets.Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=Path & Left(.Name, InStrRev(.Name, ".") - 1) & " DASHBOARD.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        Application.DisplayAlerts = True
        ActiveWorkbook.Close False
        currentSheet.Select
    End With

End Sub

Thanks again. For my own personal knowledge this code works by copying the sheets and re-pasting them into the new workbook?
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top