Backup on file open
 

Hi All. I want to backup a file when it opens. I've seen threads that tell
how to backup on close, but not on open. The problem I'm having is that when
I "FileSaveAs" the SaveAs workbook becomes the active workbook. After I
SaveAs I want to close the SaveAs and re-activate the original. Here is what
I have that doesn't work. I get a "Run-time error '9': Suscript out of range"
error message on the line "Workbooks("GFG16.xls").Activate".

Sub Auto_Open()
Worksheets("Main!A1").Select
If MsgBox("Do you want to backup before beginning?", vbYesNo) = vbNo Then
Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Program Files\Microsoft
Office\Office\Golf\GFG16Bak.xls"
Workbooks("GFG16.xls").Activate
Workbooks("GFG16Bak.xls").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

What is the best way to accomplish my task?
Thanks,
--
Jim T

Backup on file open
 

Use SaveCopyAs

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jim Tibbetts" wrote in message
...
Hi All. I want to backup a file when it opens. I've seen threads that tell
how to backup on close, but not on open. The problem I'm having is that
when
I "FileSaveAs" the SaveAs workbook becomes the active workbook. After I
SaveAs I want to close the SaveAs and re-activate the original. Here is
what
I have that doesn't work. I get a "Run-time error '9': Suscript out of
range"
error message on the line "Workbooks("GFG16.xls").Activate".

Sub Auto_Open()
Worksheets("Main!A1").Select
If MsgBox("Do you want to backup before beginning?", vbYesNo) = vbNo
Then
Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Program Files\Microsoft
Office\Office\Golf\GFG16Bak.xls"
Workbooks("GFG16.xls").Activate
Workbooks("GFG16Bak.xls").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

What is the best way to accomplish my task?
Thanks,
--
Jim T

Backup on file open
 

Thanks bob. I guess I didn't dig deep enuf in Help. Works perfectly.
--
Jim T


"Bob Phillips" wrote:

Use SaveCopyAs

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jim Tibbetts" wrote in message
...
Hi All. I want to backup a file when it opens. I've seen threads that tell
how to backup on close, but not on open. The problem I'm having is that
when
I "FileSaveAs" the SaveAs workbook becomes the active workbook. After I
SaveAs I want to close the SaveAs and re-activate the original. Here is
what
I have that doesn't work. I get a "Run-time error '9': Suscript out of
range"
error message on the line "Workbooks("GFG16.xls").Activate".

Sub Auto_Open()
Worksheets("Main!A1").Select
If MsgBox("Do you want to backup before beginning?", vbYesNo) = vbNo
Then
Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Program Files\Microsoft
Office\Office\Golf\GFG16Bak.xls"
Workbooks("GFG16.xls").Activate
Workbooks("GFG16Bak.xls").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

What is the best way to accomplish my task?
Thanks,
--
Jim T