[Excel] Question with page names

[Rev. Night]

i am trying to write a macro that when enabled, it opens up a new page and names it a month (November). I need to code it so when i click it again, another new page comes up as Decemeber, and again one comes up as Jan. and so on.

i figured the code is something like:

ActiveSheet.Name = Month + 1


but i don't knwo how to implement it. Help me.

 

[pbj75]

It is not as easy as it first appears. Accounting for duplicate sheet names doubles the amount of code. There is probably an easier way to check for duplicate sheet names, but this is all you are going to get for 30 mins of playing

Private Sub CommandButton1_Click()
Dim tempdate As Date, tempmonthname As String, namelist As Names
Set startsheet = ActiveSheet
'Get the name of the last sheet in the workbook
lastsheetname = Sheets(Sheets.Count).Name
'check to see if a dash is in the name, if so, grab all the text to the left of the dash
If (InStr(lastsheetname, "-") > 0) Then
  lastsheetname = Left(lastsheetname, InStr(lastsheetname, "-") - 1)
End If
'make the text into a full date so we can use the date functions
lastsheetname = lastsheetname + " 1,2004"
'Convert that name into a month
If (IsDate(lastsheetname)) Then
  tempdate = CDate(lastsheetname)
  tempmonth = Month(tempdate) + 1
  'if month is december, wrap back to january
  If (tempmonth > 12) Then
    tempmonth = 1
  End If
  'add a sheet
  Worksheets.Add after:=Sheets(Sheets.Count)
  'get ready to name the sheet to the next month
  basename = MonthName(tempmonth)
  tempmonthname = basename
  'need to make sure we don't name the sheet to an existing name
  j = 0
  'if name is duplicate, add "-##" to the end until the name ceases to be duplicate
  While (duplicatesheetname(tempmonthname))
    j = j + 1
    tempmonthname = basename + "-" + Str(j)
  Wend
  ActiveSheet.Name = tempmonthname
End If
startsheet.Select
End Sub
Function duplicatesheetname(thename As String) As Boolean
  duplicatesheetname = False
  For i = 1 To Sheets.Count
    If (thename = Sheets(i).Name) Then
      duplicatesheetname = True
    End If
  Next i
End Function