Creating Sheets for Months

When you are creating new workbooks, you will often have a worksheet for each month of the year.  This is a very tedious process so here is a macro that will eliminate a lot of work.

The macro below does the following:

• It first checks the names of the worksheets in your workbook
• if they begin with the letters "Sheet" It then renames them to the months of the year.
  
• If there aren’t sufficient sheets in the workbook, it adds sheets, as necessary, for each month of the year.
  

Sub DoMonths()
    Dim J As Integer
    Dim K As Integer
 
    For J = 1 To 12
        If J <= Sheets.Count Then
            If Left(Sheets(J).Name, 5) = "Sheet" Then
                Sheets(J).Name = MonthName(J)
            Else
                Sheets.Add.Move after:=Sheets(Sheets.Count)
                ActiveSheet.Name = MonthName(J)
            End If
        Else
            Sheets.Add.Move after:=Sheets(Sheets.Count)
            ActiveSheet.Name = MonthName(J)
        End If
    Next J
 
    For J = 1 To 12
        If Sheets(J).Name <> MonthName(J) Then
            For K = J + 1 To Sheets.Count
                If Sheets(K).Name = MonthName(J) Then
                    Sheets(K).Move Before:=Sheets(J)
                End If
            Next K
        End If
    Next J
 
    Sheets(1).Activate
End Sub

The final step in the macro is that it organizes the worksheets in the correct order for the months 1 through 12. If you have any other worksheets that do not begin with the word "Sheet", then these sheets end up at the end of the workbook, after the sheets for the 12 months.

Alan Salmon