Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create worksheet with the name "Jan. 2007".....
for after sheet3 try
Sheets.Add after:=Sheet3 -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... try Sub makeshts() For i = 12 To 1 Step -1 myname = Format(DateSerial(2006, i, 1), "mmm yyyy") Sheets.Add ActiveSheet.Name = myname Next i End Sub -- Don Guillett SalesAid Software "Jeffery B Paarsa" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create worksheet with the name "Jan. 2007".....
You beat me to it! here's mine anyway
Sub test() Dim i As Long, d As Long d = -15 For i = 0 To 11 d = d + 30 Worksheets.Add(after:=Worksheets(3 + i)).Name = Format(d, "mmm 2007") Next End Sub Assumes dealing with the activeworkbook, at least 3 sheets exist per your requirement, no same name sheets exist. Regards, Peter T "Don Guillett" wrote in message ... try Sub makeshts() For i = 12 To 1 Step -1 myname = Format(DateSerial(2006, i, 1), "mmm yyyy") Sheets.Add ActiveSheet.Name = myname Next i End Sub -- Don Guillett SalesAid Software "Jeffery B Paarsa" wrote in message ... 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) | |||
Is there a "create List dialog box" in Excel 2007 ? | New Users to Excel | |||
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 |