i have some VB code that i am going to use so that when a user closes the
spreadsheet it saves the original "book" and saves a copy "Book1" the next
time it saves it will increment to "book2" but also save the original.

i'm not sure if this is the right code to use or where to use it. i need to
save any changes to a new version initially then each time it is opened and
changes are carried out there is a backup copy made of the last version.

Also if a backup copy is made the VB code isn't saved with it as that copy
could be opened , change and saved producing yet another copy (if that makes
sense)

Private Sub Workbook_Open()
'Sub Save_As_NewName()


Dim strInitName As String
Dim strNewName As String
Dim intAppend As Integer
Dim myFile As String
Dim strPath

'Set the next line to required file path
strPath = "C:\XXXX\XXXXXX\Desktop\TEST"
'Note next two variables start with same value
strInitName = strPath & "\Book"
strNewName = strInitName


Do
'Dir function searches for the file description
'which has been concatenated from strNewName
'plus the file extension (First search is for
'"C:\Book1.xls")
'if file found then myFile = filename so
'therefore it exists. If not found then myFile
'is empty or = ""

myFile = Dir(strNewName & ".xls")

'If myFile is not empty then increment intAppend
'and concatenate it with strInitName and save the
'concatenated string to strNewName so on the first
'loop strNewName would become "C:\Book11".
'Note that strInitName remains unchanged.
'On the next loop if "C:\Book11" exists then
'strNewName would become "C:\Book12".
If myFile < "" Then
intAppend = intAppend + 1
strNewName = strInitName & intAppend
End If
' If intAppend 3 Then
' MsgBox "Already " & intAppend & _
' " saves of this file" & Chr(13) & _
' "Processing terminated"
'Insert your code here to handle the
'excessive saves.
' End
' End If

'Next line will cause the code to loop if myfile
'is not empty. If myFile is empty then it has not
'found the most recent strNewName therefore it does
'not exist and can be used as the new file name.

Loop While myFile < ""

ActiveWorkbook.SaveAs Filename:= _
strNewName, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub