Martin Fishlock
12/12/2006 11:30:00 PM
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.Dec."
> 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