Roy,

If you want to do it as an event before the user gets at it, I would suggest
using the FileSystemObject in a Workbook_Open event. The problem with SaveAs
is that it changes the name of the workbook being worked upon (to whatever
was specified in SaveAs).

Private Sub Workbook_Open()

Dim oFSO As Object
Dim sThisFile As String
Dim sBackup As String

sThisFile = ThisWorkbook.FullName
sBackup = Left(sThisFile, Len(sThisFile) - 3) & Format(Now, "dd mmm yyyy
hh-mm-ss") & ".bak"

Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.copyfile sThisFile, sBackup
Set oFSO = Nothing

End Sub

The reason I use FSO rather than FileCopy is that FileCopy does not work on
an open file, which you need in this case.

The file saved is the original filename, without the 'xls' extension, and
with a date and time appended, and finally '.bak.' This will leave you as
many versions as you want to keep in the same folder.

This code is workbook code, so it goes in the ThisWorkbook code module.

--

HTH

Bob Phillips

"Roy" wrote in message
...
Mr. Walkenbach didn't cover those in his book (Excel 2000
Power Programming with VBA), or at least not in a way
that I could understand!

Could you provide some more detail or a reference site?


-----Original Message-----
There are many ways to do this. Have you thought of
using saveas or sheet
copy?

"Roy" wrote in message
...
I'm developing an Excel-based VBA program that performs
some calculations and updates on a master budget sheet.

However, before I allow the user to go modifying this
sheet, I would like to "back it up" to a new Excel
spreadsheet in the very-likely event I have to correct
their errors.

How do you copy/export/backup the entire workbook
and/or
selected sheets to a new Excel workbook?

Thanks in advance for your help!


.