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