Oct 20 2020 12:58 PM
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?
Oct 20 2020 01:28 PM
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 Sub
Oct 21 2020 03:53 PM
Oct 21 2020 04:03 PM
Hans,
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?
Oct 22 2020 01:27 AM
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
Oct 22 2020 06:41 AM
Hans, it worked....The created worksheets are blank but I can copy the main template into them. But, is there a syntacts that I can insert into the macro to tell it to copy my main template at the same time?
Oct 22 2020 06:51 AM
Is the main template a sheet in the same workbook in which you want the monthly sheets?
What is its name?
Oct 22 2020 07:01 AM
Yes, sasme workbook. I got it to work. As I said in last reply, creates month named blank sheets. Is there a way to insert some code to make it copy the Master Worksheet also?
Oct 22 2020 07:43 AM
Since you didn't explicitly answer my question, I will assume that the master worksheet is named Master Worksheet.
Sub AddYearly()
Dim wbk As Workbook
Dim wsh As Worksheet
Dim i As Long
Application.ScreenUpdating = False
Set wbk = ActiveWorkbook
Set wsh = wbk.Worksheets("Master Worksheet")
For i = 1 To 12
wsh.Copy After:=wbk.Worksheets(wbk.Worksheets.Count)
wbk.Worksheets(wbk.Worksheets.Count).Name = MonthName(i)
Next i
Application.ScreenUpdating = True
End Sub
Oct 22 2020 12:33 PM
Sorry: the name of the master is WORKSHEET TEMPLATE.
Oct 22 2020 12:46 PM
Oct 22 2020 04:22 PM
Thanks. getting there, thanks to your help.
Oct 23 2020 07:54 AM
Hans, it worked great. Thanks much. See attachment May I ask your help with other tasks as I create this workbook?