It is January, the start of a new year. As such you may find you need to add 12 new worksheets, one for each month of the year, to your existing workbook. But this year you decide you are sick of doing monotonous, repetitive, mindless work of open the workbook, adding a new sheet, renaming it January, adding another new sheet, naming it February, etc. etc. until you have created a sheet for all 12 months of the year. Isn’t there a better way?
The answer is yes. You can have a VBA macro do the tedious work for you. Just follow these steps to create the macro.
How to create the macro
- Open Excel.
- To open the VBA Editor, press Alt + F11.
- To display the Project Explorer, select View > Project Explorer from the menu.
- To display the Project Explorer’s folders, if necessary expand the VBA Project (PERSONAL.XLSB) with the plus sign, and then, if needed, expand the Modules with its plus sign.
- To add a new module, first click on the Modules node, and then select Insert > Module from the menu.
- Copy and paste the code below into the new module.
- To save the Personal Macro Workbook, press Ctrl + S or select File > Save Personal.XLSB from the menu.
- To return to Microsoft Excel, press Alt + Q or select File > Close and Return to Microsoft Excel.
[vb]
Sub AddMonthlySheets()
'Create three variables
Dim arrMonthNames As Variant 'This variable will hold an array (list) of month names.
Dim i As Integer 'This is counting variable used to loop through the month names.
Dim wksNewSheet As Worksheet 'This is a object variable that represents the new sheet that is added.
'Create an array of month names.
arrMonthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
'Suppress error messages and instead go to the next statement.
On Error Resume Next
'Start a loop that goes from the first month to the last month.
For i = LBound(arrMonthNames) To UBound(arrMonthNames)
'Add the new sheet.
Set wksNewSheet = Sheets.Add(After:=Sheets(Sheets.Count))
'Rename the new sheet.
wksNewSheet.Name = arrMonthNames(i)
'If error number 1004 occurs because the sheet already exists, then...
If Err.Number = 1004 Then
'Display a message that a sheet for the said month already exists.
MsgBox "Sorry, a sheet for " & arrMonthNames(i) & " already exists.", vbCritical, "TechMentor Macros"
'Clear the error.
Err.Clear
'Turn off warning messages.
Application.DisplayAlerts = False
'Delete the sheet that was created.
wksNewSheet.Delete
'Turn on warning messages.
Application.DisplayAlerts = True
End If
'Process the next item in the array.
Next i
End Sub
[/vb]
How to use the macro
- Open Excel.
- To view the list of macros press Alt + F8 or select View > Macros > View Macros from the ribbon.
- To launch the macro either click the PERSONAL.XLS!AddMonthlySheets and then click run
or just double click the PERSONAL.XLS!AddMonthlySheets macro.
Excel will then add 12 sheets to your workbook, one for each month of the year.