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