Macro to create new worksheet

Hi guys,

I need a macro to generate a new worksheet with the name one month after the last worksheet... i.e. I have worksheets called, January 2007, February 2007, March 2007, now I want a macro that will automatically create April 2007 and so on. It would be best if the worksheet could be a copy of another worksheet simply with a different name? Possible?

Hello ChrisMattock,

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hi,

It will work for you.

Sub CreateNewMonth()
Dim NewMonth, NewYear As Integer
Dim NewSheetName As String
Dim MonthName As String
With ThisWorkbook
oldmonth = .Worksheets(.Worksheets.Count).Name
NewMonth = Month(DateSerial(Year(oldmonth), Month(oldmonth) + 1, 1))
NewYear = Year(DateSerial(Year(oldmonth), Month(oldmonth) + 1, 1))
If NewMonth > 12 Then
NewYear = NewYear + 1
NewMonth = 1
End If
NewSheetName = Choose(NewMonth, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") & " " & NewYear
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
With ActiveSheet
.Name = NewSheetName
End With
End With
End Sub

Regards,
Gajendra Gupta