Hi Kristabelle,
Here is a macro that when you run it will add one to the file name of the active workbook and do a SaveAs. To install it, use Alt-TMV and paste the code into the macro module code pane that appears. To make its use easy you can assign this macro to a button or, using the instructions commented in the code, make it the workbook's default Save mode.
Sub IncrementAndSaveAs()
'This macro saves the active workbook, which is assumed to have
'a filename composed of a name and a number, such as "Inventory060.xls".
'This macro increments the filename to Inventory061.xls, and does a
'SaveAs to this filename. To make this macro execute automatically
'on each save, use the Workbook_Save event macro with the code:
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' If Not SaveAsUI then
' SaveAsUI = False
' Cancel = False
' IncrementAndSaveAs
' EndIf
'End Sub
'Note: The number of numeric digits you use in the file name determines
' how many saves can be done before the number wraps to zero. For
' example, after Inventory999.xls would be Inventory000.xls, so three
' digits allows for 999 saves before wrapping to zero.
'find out how many digits in current filename
Dim CurrentName As String
Dim NewName As String
Dim nLen As Integer
Dim NewNumber As String
CurrentName = ActiveWorkbook.Name
If Right(CurrentName, 4) = ".xls" Then
'strip off .xls
CurrentName = Left(CurrentName, Len(CurrentName) - 4)
End If
For nLen = 6 To 1 Step -1
NewNumber = Right(CurrentName, nLen)
If IsNumeric(NewNumber) Then
GoTo SetNewName
End If
Next nLen
MsgBox "File name does not end with numeric string" & vbCrLf & _
"New numeric version cannot be created.", _
vbCritical, "Save Not Done"
Exit Sub
SetNewName:
NewName = Left(CurrentName, Len(CurrentName) - nLen) & _
Right(CStr(CLng(NewNumber) + 1), nLen)
ActiveWorkbook.SaveAs Filename:=NewName
End Sub