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

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:

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

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.