Need Help w/ Code

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

Its probably because u r using =month(now())-1, this equates to Jan - 1,
which although Jan minus 1 month is Dec, Excel interprets as 1-1 = 0 (i.e.
Jan -1). Hence the case select does not work as there is no branch for zero.
Try
Select Case DATE(YEAR(NOW()),MONTH(NOW())-1,DAY(NOW()))

Its untested may not work as code but its work as an excel formula so should
convert easy enough.

HTH

"Ronbo" wrote:

> 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
>
>

Another option:

Select Case Month(Date)
Case 1
tmpMonth = "Dec "
Case 2
tmpMonth = "Jan "
Case 3
tmpMonth = "Feb "
Case 4
tmpMonth = "Mar "
'etc, etc, etc
Case 12
tmpMonth = "Nov "
End Select

But there are a few more options:

Option Explicit

'using Jimbola's suggestion
Sub testme()
Dim tmpMonth As String
Select Case Month(DateSerial(Year(Date), Month(Date) - 1, 1))
Case Is = 1: tmpMonth = "Jan "
'etc, etc, etc
Case Is = 12: tmpMonth = "Dec "
End Select
MsgBox tmpMonth
End Sub

'this seems easiest to me.
Sub testme2()
Dim tmpMonth As String
tmpMonth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "MMM ")
MsgBox tmpMonth
End Sub

'and if you have xl2002 (if I remember correctly, Monthname was added then)
Sub testme3()
Dim tmpMonth As String
tmpMonth = MonthName(Month(DateSerial(Year(Date), Month(Date) - 1, 1)), _
abbreviate:=True) & " "
MsgBox tmpMonth
End Sub

(I thing

Ronbo wrote:
>
> 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

--

Dave Peterson