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
--
Traa Dy Liooar

Jock

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

--
Gary''s Student - gsnu200835


"Jock" wrote:

> 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
> --
> Traa Dy Liooar
>
> Jock

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Jock" <(E-Mail Removed)> wrote in message
news:66D07AC2-B0EF-45A9-A20C-(E-Mail Removed)...
> 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
> --
> Traa Dy Liooar
>
> Jock