Forum Discussion
Macro for naming sequential worksheets
I often have to create a Yearly workbook with worksheet tabs containing the names of each of the 12 consecutive months. Is there a way to create a macro to automatically do this?
You could create such a workbook manually, then save it as a template (.xltx).
You can then double-click the template to create a new copy of it.
If you prefer a macro:
Sub CreateYearly()
Dim wbk As Workbook
Dim wsh As Worksheet
Dim i As Long
Application.ScreenUpdating = False
Set wbk = Workbooks.Add(Template:=xlWBATWorksheet)
Set wsh = wbk.Worksheets(1)
wsh.Name = MonthName(12)
For i = 11 To 1 Step -1
Set wsh = wbk.Worksheets.Add(Before:=wsh)
wsh.Name = MonthName(i)
Next i
Application.ScreenUpdating = True
End SubHans,
the macro worked but it did not create worksheets within my intended workbook; instead, it created a new workbook with only the sequential sheets in it. any ideas?
Your question started with "I often have to create a Yearly workbook", so the code creates a workbook...
Here is a version that adds 12 sheets to the active workbook.
Sub AddYearly() Dim wbk As Workbook Dim wsh As Worksheet Dim i As Long Application.ScreenUpdating = False Set wbk = ActiveWorkbook Set wsh = wbk.Worksheets(wbk.Worksheets.Count) For i = 1 To 12 Set wsh = wbk.Worksheets.Add(After:=wsh) wsh.Name = MonthName(i) Next i Application.ScreenUpdating = True End Sub
