Macro for naming sequential worksheets

Brass Contributor

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?

13 Replies

@ksmith 

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

@Hans Vogelaar 

Sorry, I took the macro from the email,not from your reply.  will try it.

 

@ksmith 

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?

@ksmith 

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

@Hans Vogelaar 

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?

@ksmith 

Is the main template a sheet in the same workbook in which you want the monthly sheets?

What is its name?

@Hans Vogelaar 

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?

@ksmith 

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

@Hans Vogelaar 

Sorry:  the name of the master is WORKSHEET TEMPLATE.

@ksmith 

 

You only need to change "Master Worksheet" to "Worksheet Template" in the code.

@Hans Vogelaar 

Thanks.  getting there, thanks to your help.

@Hans Vogelaar 

Hans, it worked great.  Thanks much. See attachment   May I ask your help with other tasks as I create this workbook?

@ksmith 

Sure, feel free to do so.