VBA to save a .XLSM file as two different names in same folder and continue code uninterrupted

[jblonde002]

Sorry for title, tried my best!

I have a workbook called DPD.xlsm. I would like it that every time a user presses "save" it overwrites a different .xlsm workbook called "Import.xlsm" that always resides in the same folder directory it also needs to then save a second time as it normally would (DPD.xlsm). This has to be with no overwrite confirmation popups.

I have next to zero experience writing VBA and have visited a lot of forums but can't see how this is possible? Thanks in advance for anyone that can help!

 

[jblonde002]

Not sure if anyone knows if this is possible or impossible? At this stage just knowing whether to give up with my hunt for the answer if someone could confirm it's impossible!

 

[Steve_]

Not sure if anyone knows if this is possible or impossible? At this stage just knowing whether to give up with my hunt for the answer if someone could confirm it's impossible!

is the second workbook just a copy of the workbook you are saving?

if so, this will do what you need.

Public isSaving As Boolean
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Not isSaving Then
        isSaving = True
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\example.xlsm"
        Application.DisplayAlerts = True
        isSaving = False
    End If
End Sub

Paste it into the "ThisWorkbook" code module.

 

[jblonde002]

Thank you so much!!!

I have done a slight edit as below to save it back as DPD.xlsm but this is so helpful - I appreciate your time and wisdom lots after trying this for hours!

Public isSaving As BooleanPrivate Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Not isSaving Then
        isSaving = True
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & "\import.xlsm"
        ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & "\DPD.xlsm"
        Application.DisplayAlerts = True
        isSaving = False
    End If
End Sub

 

[John_w]

Use SaveCopyAs instead of the first SaveAs and then you don't need the second SaveAs:

ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & "\Import.xlsm"

 

[Steve_]

Thank you so much!!!

I have done a slight edit as below to save it back as DPD.xlsm but this is so helpful - I appreciate your time and wisdom lots after trying this for hours!

Public isSaving As BooleanPrivate Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Not isSaving Then
        isSaving = True
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & "\import.xlsm"
        ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & "\DPD.xlsm"
        Application.DisplayAlerts = True
        isSaving = False
    End If
End Sub

Happy to help. No thanks needed. Be sure to click that like button tho. :P