June 3, 2021

Adding Month Sheets to Workbook Automatically

How to add the 12 months worksheets to a workbook automatically using VBA. Watch the video below:

Adding Month Sheets to Workbook Automatically with VBA

Often we need to add sales data, students performance data, monthly expenses and income or purchase data in worksheets based on the month name. We can add the worksheets for each month of the year manually but with time this can become cumbersome. Another super easy method to add the month worksheets is to use a macro. Now the VBA code automates the whole process and we can add the sheets for the 12 months quickly and easily. The macro code below does the job easily:

Sub AddMonthSheets()
Dim i As Integer
Dim j As Integer

For i = 1 To 12
    If i <= Sheets.Count Then
        If Left(Sheets(i).Name, 5) = "Sheet" Then
            Sheets(i).Name = MonthName(i)
        Else
            Sheets.Add.Move after:=Sheets(Sheets.Count)
            ActiveSheet.Name = MonthName(i)
        End If
    Else
        Sheets.Add.Move after:=Sheets(Sheets.Count)
        ActiveSheet.Name = MonthName(i)
    End If
Next i

For i = 1 To 12
    If Sheets(i).Name <> MonthName(i) Then
        For j = i + 1 To Sheets.Count
            If Sheets(j).Name = MonthName(i) Then
                Sheets(j).Move Before:=Sheets(i)
            End If
        Next j
    End If
Next i

Sheets(1).Activate

End Sub
Add Sheets for 12 Months in Workbook
Add Sheets for 12 Months in Workbook

When the macro is executed all sheets beginning with ‘sheet’ are replaced. If you have more than 12 worksheets with the names beginning with ‘sheet’ then only the first 12 will be assigned the appropriate names of the month. If you have less than 12 sheets beginning with the name ‘sheet’ then all of them will be replaced and the required balance shets with the month names will be added.

If you have sheets with different names like ‘ProfitLoss’ they will be placed after the worksheets with the month names. Also the month sheets will be arranged in a proper order from January to December.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.