Save a copy of a workbook when saving active version

[sethness15]

Hi All,

I think I'm going about this right but there's just something slightly wrong with my code.. I'm trying to have excel save a copy of my workbook to a specific directory when the active file is saved. Here's what I have..


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="C:\Users\me\Desktop\Copy.xls", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub


It's saving the file but excel stops working afterwards.

Please let me know if you have any suggestions.

Thanks!

 

[dmt32]

Hi,
try using SaveCopyAs method:

ActiveWorkbook.SaveCopyAs "C:\Users\me\Desktop\Copy.xls"

Dave

 

[sethness15]

Hi Dave,

Still having the same issue where excel stops working. It seems to work the first time I save but gets screwed up when the copy already exists and I make a change to the original file and try to save.. Once I get it working, I'd like to make the message about the copy already existing to not pop up. (I want to automatically save over it.)

This is my code now:

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

ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:="C:\Users\me\Desktop\Copy.xlsm"

End Sub

Thanks,
Seth

Hi,
try using SaveCopyAs method:

ActiveWorkbook.SaveCopyAs "C:\Users\me\Desktop\Copy.xls"

Dave