Executing on Save

[turbo805]

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!

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

 

[Logit]

.
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.

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

 

[turbo805]

.
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).

 

[John_w]

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.

 

[turbo805]

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.

 

[John_w]

Glad you got it working!

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

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

 

[turbo805]

Glad you got it working!

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

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?

 

[John_w]

Yes, it selects all the sheets (a group), copies them to a new workbook and saves that workbook as .xlsx.