Copy worksheet and rename to next month

[Jock]

When sheet called 'January' is complete, I have set up a command button which
when clicked, will create a new sheet which is to be named as the following
month - in this case 'February'.
How do I tweak the following code to name the new sheet by the correct month?

Sub CopyShtPlus1Month()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = ActiveSheet.Name + WHAT DO I ADD HERE???
If Not SheetExists(newShtName) Then
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(newShtName).Activate
Range("C7:M43").Select
Selection.ClearContents
Range("C9").Select
Else
MsgBox "You've already created a sheet for this month.", vbCritical
End If

End Sub

Thanks for suggestions

 

[Gary''s Student]

Just create an array and use the next item in the array:

Sub CopyShtPlus1Month()
Dim shtName As String, newShtName As String
Dim s(20) As String
s(0) = "January"
s(1) = "February"
s(2) = "March"
s(3) = "April"
s(4) = "May"
s(5) = "June"
s(6) = "July"
s(7) = "August"
s(8) = "September"
s(9) = "October"
s(10) = "November"
s(11) = "December"
shtName = ActiveSheet.Name
For i = 0 To 10
If shtName = s(i) Then
newShtName = s(i + 1)
Exit For
End If
Next

 

[Don Guillett]

Try this idea

Sub shtName()
Dim x, y
x = Month(DateValue("1/" & ActiveSheet.Name & "/2009")) + 1
y = Format(DateValue(x & "/1/2008"), "mmmm")
'MsgBox x
'MsgBox y
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = y
Range("C7:M43").ClearContents

End Sub