Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code not working and can't see why | Excel Discussion (Misc queries) | |||
Often-Used Code not working in a new Workbook | Excel Discussion (Misc queries) | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) | |||
Problem Code: Retrieving Stored Access 03 Query | Excel Discussion (Misc queries) | |||
VBA Newbie: Help with Do Loop code | Excel Discussion (Misc queries) |