Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create worksheet with the name "Jan. 2007".....
Jeffery
Firstly you had provate which means you cannot see it to run. Here is one that works. It uses the internal dates and asks for the year. If this is an internal sub then you can change it to Year(Date) + 1. If it is to be run on non english windows then you may want to change the date to the array but becareful with the base 0 or 1 start. Sub SchedSheets() Dim mm As Integer Dim yy As Integer yy = InputBox("Year?", "Enter year...", Year(Date) + 1) For mm = 1 To 12 Worksheets.Add after:=Sheets(Worksheets.Count) ActiveSheet.Name = Format(DateSerial(yy, mm, 1), "mmm yyyy") Next mm End Sub -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Jeffery B Paarsa" wrote: Hello all, In a VBA macro I running the following code hoping to create a maximum number of 12 worksheets after worksheet number 3 with the worksheet name of "Jan. 2007", "Feb. 2007"....etc... What am I doing wrong... Beleive me I spent at least 2 hours in search to find an example code with no success.... Private Sub SchedSheets() Dim mon As String Dim monArr() As String mon = "Jan.Feb.Mar.Apr.May.Jun.Jul.Aug.Sep.Oct.Nov.D ec." monArr = Split(mon, ".") Dim ws As Worksheet Dim mm As Integer Dim i As Integer i = 3 m = Month(Date) Dim sheetName As String While mm < 13 sheetName = monArr(m) & " " & Year(Date) Worksheets.Add after:=Sheets(i) ActiveWorksheet.Name = sheetName i = i + 1 m = m + 1 Wend End Sub -- Jeff B Paarsa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto create "backup of filename.xls" in Excel 2007. | Excel Discussion (Misc queries) | |||
How to create worksheet with the name "Jan. 2007"..... | Excel Programming | |||
How to create worksheet with the name "Jan. 2007"..... | Excel Programming | |||
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error | Excel Discussion (Misc queries) | |||
create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions |