Jim Cone has actually given what is probably the easiest and safest way to do
it, simply use File | Save As and give it a new name.
Now, if you really want it to be automated, here's some VBA code to stick
into the Workbook's Open event (if you have questions on how to get to the
right place for the code, just ask) that will do it for you automatically.
Hopefully the code is commented enough so you can see what's going on, but to
recap: the first time you open a file in a new month, could be on the 1st or
maybe later in the month, as on the 30th!, it checks the date to see if
YEAR/Month has changed to a later date. If it has, then th existing file is
saved, but with an "a" stuck in the filename so that if you were to open that
"archived" file sometime down the road, it won't get renamed again. After
saving with the archive name, it kills the original file off, and gives
itself a new name reflecting the current year/month.
As noted in the comments, the filename MUST take on the form of some
phrase/name that ends with _YYYYMM.xls, where YYYY is a 4-digit year, and MM
is a 2-digit month. So for testing, you can start off with a name like
TestAutoRename_200812.xls and once the code is in it, and you save, close
and open it, you should find that there's a TestAutoRename_200812a.xls in the
folder, that TestAutoRename_200812.xls no longer exists in it, and the file
you're working with has suddenly become named TestAutoRename_200901.xls
Here's the code:
Private Sub Workbook_Open()
'assumes naming convention like
' phrase, underscore, YYYYMM, i.e.
' myWorkbook_200901.xls
'AND that when a workbook is
'auto-archived, that the filename has
'an "a" following the month portion of the name, as
' myWorkbook_200901a.xls
'
Dim currentWBName As String
Dim newWBName As String
Dim fileToKill As String
Dim oldYear As Integer
Dim oldMonth As Integer
'is this an 'archive' workbook, if so, do nothing
If Mid(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, _
".") - 1, 1) = "a" Then
Exit Sub
End If
oldYear = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 1, 4)
oldMonth = Mid(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, "_") + 5, 2)
If (oldYear < Year(Now())) Or _
(oldYear = Year(Now()) And oldMonth < Month(Now())) Then
'oldYear < Year(Now()) tests if we've gone from Dec to Jan
'old Year = Year(Now()) and oldMonth<Month(now()) tests
'if we have moved to a new month in the current year
fileToKill = ThisWorkbook.FullName
newWBName = Left(fileToKill, _
InStrRev(fileToKill, ".") - 1)
newWBName = newWBName & "a."
newWBName = newWBName & Right(fileToKill, _
Len(fileToKill) - InStrRev(fileToKill, "."))
'this workbook gets saved with the "a" added to the name
ThisWorkbook.SaveAs newWBName
'the old copy, without the "a" gets killed/Deleted
Kill fileToKill
'now give it a new name based on current YEAR/Month
currentWBName = Left(newWBName, _
InStrRev(newWBName, "_")) & Trim(Str(Year(Now()))) & _
Format(Month(Now()), "00") & "." & _
Right(newWBName, Len(newWBName) - InStrRev(newWBName, "."))
ThisWorkbook.SaveAs currentWBName
'voila! the workbook has new name needed
End If
End Sub
