BeforeClose event - Savings

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I want to save a copy of a spreadsheet in a different location, after I
update it. I put code in the BeforeClose event, so it saves the file in the
desired location.

The problem I have is it saves the BeforeClose event also. Is there anyway
to make a backup copy of the file and not include the code in the backup
copy?
 
Here is a backup macro I use.

Sub Backup() 'kept in personal.xls & assigned to toolbar button
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub
 
I'm sure the is an easier way, but I had the same problem
and came up with this solution.

Copy each of the worksheets to a new workbook and then
save that workbook as your backup. This will remove ALL
of your code, so if you want to keep some, then you'll
have to find another way.
 
Mike said:
I want to save a copy of a spreadsheet in a different location, after I
update it. I put code in the BeforeClose event, so it saves the file in the
desired location.

The problem I have is it saves the BeforeClose event also. Is there anyway
to make a backup copy of the file and not include the code in the backup
copy?
Click to expand...
You can probably work around this by reopening the backup copy after it has
been saved, and then using sendkeys to activate the VBE and delete the code.
Bit tricky though.
 
Back
Top