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