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

"Jock" wrote in message
...
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