Easiest way to expand 1 spreadsheet to a workbook of 12.

[Guest]

If I want to take one master sheet and turn it into 11 more for each month of
the year, what is the easiest way to copy the same sheet for 12 months worth
of Tabs?

 

[Don Guillett]

to name the sheets 1,2,3,4,etc
Sub copytemplate()
For i = 1 To 11 'or InputBox("how many")
Sheets("first").Copy after:=Sheets(Worksheets.Count)
activesheet.name="Month " & i
Next i
End Sub

 

[Gord Dibben]

Shady

Create a new sheet named List and enter Jauary through December in A1:A12

Change "Template" to "Master" in the code below or temporarily changing your
"Master" sheet to "Template" may be easiest.

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("List")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell

End Sub


Gord Dibben MS Excel MVP