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:
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
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.
