Disable Events not preventing crash....

[paipimenta]

This was offered as a solution to another's post...
http://www.mrexcel.com/forum/showthread.php?t=465898
but I got a bug in it. I've got the following code to save a copy of the current file to another location...
(To test this out, I used my Desktop[shared] and My Documents[master] as the two locations)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If ActiveWorkbook.FullName = "S:\SharedPath\SharedWBook.xls" Then
        Application.EnableEvents = False
        Dim originalFilename As String
        originalFilename = "S:\SharedPath\SharedWBook.xls"
 
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs ("D:\MasterPath\MasterWBook.xls")
        ActiveWorkbook.SaveAs (originalFilename)
        Application.DisplayAlerts = True
 
        Application.EnableEvents = True
 
    End If
End Sub

Users operate in a shared workbook and when they save, it also saves to a master, non-shared wbook in a separate location. Using 2003, I'm getting an Excel crash 80%-90% of the time. I thought disabling events would fix it, but it hasn't. Help?

 

[Richard Schollar]

Rather than saving it twice, I think you'd be best off using SaveCopyAs method:

ActiveWorkbook.SaveCopyAs "D:\MasterPath\MasterWBook.xls"

That way you won't need to resave it as its original filename.