Save a duplicate copy in excel using VBA

[VBnewb13]

For some reason, excel stops working when I use this code...

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

ThisWorkbook.SaveAs "C:\Users\Me\Desktop\test2\test2.xlsm"

End Sub

What I am trying to do is save a duplicate copy of my workbook in another location. Is there maybe another way to go about doing this? I am really new to VBA so odds are I'm making mistakes here and there. Any help or direction would be greatly appreciated :)

Thanks

[Stewart Ross]

Hi. You are trying to use the BeforeSave event of the workbook to try to save the current workbook to a new location. This can't work, as BeforeSave is triggered every time you try to save the workbook and it will try again to save a copy, triggering itself once again, and leading to an infinite regression of incomplete save events locking the whole application!

If you want to save a duplicate copy you will need an entirely different approach. Bear in mind that the name you give to SaveAs is the name of that workbook thereafter - it does not save a duplicate copy as such.

You would need to design a bespoke replacement for the existing File Save and SaveAs methods. Aside from placing a new Save button on your application you would need to use the BeforeSave event as the trigger for your new events too, by cancelling the normal file save and redirecting code execution to your new routines. I think this task is likely to prove too much of a challenge given that you have limited experience in VBA, and I would advise you not to go down this route at present.

-Stewart