How to rename worksheet like "Jan. 2006"
 

I modified your code a bit to work.

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
Worksheets(3).Select
m = Month(Date)
Dim sheetName As String
While m <= UBound(monArr)
If monArr(m - 1) < "" Then
sheetName = monArr(m - 1) & " " & Year(Date)
Worksheets.Add after:=ActiveSheet
ActiveSheet.Name = sheetName
m = m + 1
End If
Wend
End Sub

keizi

"Jeffery B Paarsa" wrote in message
...
Sorry guys before I enter the text I accidently hit the enter key... Here
is
the question:

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


"Jeffery B Paarsa" wrote:


--
Jeff B Paarsa