Macro for naming sequential worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1801117%22%20slang%3D%22en-US%22%3EMacro%20for%20naming%20sequential%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1801117%22%20slang%3D%22en-US%22%3E%3CP%3EI%20often%20have%20to%20create%20a%20Yearly%20workbook%20with%20worksheet%20tabs%20containing%20the%20names%20of%20each%20of%20the%2012%20consecutive%20months.%26nbsp%3B%20Is%20there%20a%20way%20to%20create%20a%20macro%20to%20automatically%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1801117%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1801246%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20for%20naming%20sequential%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1801246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93610%22%20target%3D%22_blank%22%3E%40ksmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20create%20such%20a%20workbook%20manually%2C%20then%20save%20it%20as%20a%20template%20(.xltx).%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20double-click%20the%20template%20to%20create%20a%20new%20copy%20of%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20prefer%20a%20macro%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20CreateYearly()%0A%20%20%20%20Dim%20wbk%20As%20Workbook%0A%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20wbk%20%3D%20Workbooks.Add(Template%3A%3DxlWBATWorksheet)%0A%20%20%20%20Set%20wsh%20%3D%20wbk.Worksheets(1)%0A%20%20%20%20wsh.Name%20%3D%20MonthName(12)%0A%20%20%20%20For%20i%20%3D%2011%20To%201%20Step%20-1%0A%20%20%20%20%20%20%20%20Set%20wsh%20%3D%20wbk.Worksheets.Add(Before%3A%3Dwsh)%0A%20%20%20%20%20%20%20%20wsh.Name%20%3D%20MonthName(i)%0A%20%20%20%20Next%20i%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1806356%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20for%20naming%20sequential%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806356%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93610%22%20target%3D%22_blank%22%3E%40ksmith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHans%2C%3C%2FP%3E%3CP%3Ethe%20macro%20worked%20but%20it%20did%20not%20create%20worksheets%20within%20my%20intended%20workbook%3B%20instead%2C%20it%20created%20a%20new%20workbook%20with%20only%20the%20sequential%20sheets%20in%20it.%26nbsp%3B%20any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1808253%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20for%20naming%20sequential%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1808253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHans%2C%20it%20worked....The%20created%20worksheets%20are%20blank%20but%20I%20can%20copy%20the%20main%20template%20into%20them.%26nbsp%3B%20But%2C%20is%20there%20a%20syntacts%20that%20I%20can%20insert%20into%20the%20macro%20to%20tell%20it%20to%20copy%20my%20main%20template%20at%20the%20same%20time%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

@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
Highlighted

@Hans Vogelaar 

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

 

Highlighted

@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?

Highlighted

@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
Highlighted

@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?

Highlighted

@ksmith 

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

What is its name?

Highlighted

@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?

Highlighted

@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
Highlighted

@Hans Vogelaar 

Sorry:  the name of the master is WORKSHEET TEMPLATE.

Highlighted

@ksmith 

 

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

Highlighted

@Hans Vogelaar 

Thanks.  getting there, thanks to your help.

Highlighted

@Hans Vogelaar 

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

Highlighted

@ksmith 

Sure, feel free to do so.