G
Guest
I have a routine that uses the name of the workbook to create a new workbook
for the current month. It has worked perfectly through the year til now.
Maybe it has something to do with the change of the year?
Old workbook name = JOHN REPORT Nov 04 - WB w/macro to create new WB
New workbook name should be = JOHN REPORT Dec 04
Actual name the routine creates now is = JOHN REPORT 04 - with no month.
The code is:
tmpName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 10)
Select Case Month(Now()) - 1
Case 1
tmpMonth = "Jan "
Case 2
tmpMonth = "Feb "
Case 3
tmpMonth = "Mar "
Case 4
tmpMonth = "Apr "
Case 5
tmpMonth = "May "
Case 6
tmpMonth = "Jun "
Case 7
tmpMonth = "Jul "
Case 8
tmpMonth = "Aug "
Case 9
tmpMonth = "Sep "
Case 10
tmpMonth = "Oct "
Case 11
tmpMonth = "Nov "
Case 12
tmpMonth = "Dec "
End Select
tmpYear = Right(Year(Now()) - 1, 2)
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\Name\MyDocuments\PRC JOHN\" _
& tmpName & tmpMonth & tmpYear, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Any help would truly be appreciated.
Thanks
for the current month. It has worked perfectly through the year til now.
Maybe it has something to do with the change of the year?
Old workbook name = JOHN REPORT Nov 04 - WB w/macro to create new WB
New workbook name should be = JOHN REPORT Dec 04
Actual name the routine creates now is = JOHN REPORT 04 - with no month.
The code is:
tmpName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 10)
Select Case Month(Now()) - 1
Case 1
tmpMonth = "Jan "
Case 2
tmpMonth = "Feb "
Case 3
tmpMonth = "Mar "
Case 4
tmpMonth = "Apr "
Case 5
tmpMonth = "May "
Case 6
tmpMonth = "Jun "
Case 7
tmpMonth = "Jul "
Case 8
tmpMonth = "Aug "
Case 9
tmpMonth = "Sep "
Case 10
tmpMonth = "Oct "
Case 11
tmpMonth = "Nov "
Case 12
tmpMonth = "Dec "
End Select
tmpYear = Right(Year(Now()) - 1, 2)
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\Name\MyDocuments\PRC JOHN\" _
& tmpName & tmpMonth & tmpYear, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Any help would truly be appreciated.
Thanks