VBA Code


Sub Insert_Month_Sheet()
' Written by Barrie Davidson
Dim Month_Number As Integer
Dim Month_Text(12) As String
Dim Sheet_Name As String
Dim answer As Integer
Dim Sheet_count As Variant
Dim Counter As Integer

   Month_Text(1) = "January"
   Month_Text(2) = "February"
   Month_Text(3) = "March"
   Month_Text(4) = "April"
   Month_Text(5) = "May"
   Month_Text(6) = "June"
   Month_Text(7) = "July"
   Month_Text(8) = "August"
   Month_Text(9) = "September"
   Month_Text(10) = "October"
   Month_Text(11) = "November"
   Month_Text(12) = "December"
   Month_Number = Month(Date)
   Sheet_Name = Month_Text(Month_Number)
   answer = MsgBox("Add worksheet for " & Sheet_Name, vbYesNo)
      If answer = 7 Then
         Exit Sub
      Else
      End If
   Sheet_count = ActiveWorkbook.Sheets.Count
   Counter = 1
   Do Until ActiveSheet.Index = Sheet_count
      Sheets(Counter).Select
         If ActiveSheet.Name = Sheet_Name Then
            answer = MsgBox("A sheet already exists for " & Sheet_Name & ". Continue?", _
               vbYesNo + vbQuestion)
               If answer = 7 Then
                  Exit Sub
               Else
                  Sheets.Add
                  ActiveSheet.Name = Sheet_Name & " (2)"
                  Exit Sub
               End If
         Else
         End If
      Counter = Counter + 1
   Loop
   Sheets.Add
   ActiveSheet.Name = Sheet_Name

End Sub