Save the document created with VBA

[S Oberlander]

My excel file is blocked from savings with a beforeclose even handler, however, to offer an alternative way to save I created the below sub.
It works great when I step thru it, but when I just click on the button linked to it, it tries to save the blocked workbook so nothing gets saved...

Sub savecopy()

    Sheets("Pivot").Copy
    ActiveSheet.Shapes.Range(Array("Button 1")).Delete
    With Application.FileDialog(msoFileDialogSaveAs)
        .InitialFileName = ThisWorkbook.Path & "\"
        If .Show Then .Execute
    End With
    
End Sub

 

[Joe4]

You may need to temporarily disable events so that your save attempt does not trigger your BeforeSave event to fire.
See if this works:

Sub savecopy()

    Sheets("Pivot").Copy
    ActiveSheet.Shapes.Range(Array("Button 1")).Delete
    Application.EnableEvents = False
    With Application.FileDialog(msoFileDialogSaveAs)
        .InitialFileName = ThisWorkbook.Path & "\"
        If .Show Then .Execute
    End With
    Application.EnableEvents = True
    
End Sub

 

[S Oberlander]

The things is, that when I step thru the save-as is applying to the newly created workbook, but when I click on the button the save-as is applying to the initial workbook.
So when I used Application.EnableEvents, it simply did a save-as on the blocked workbook.