worksheet template

[MJKelly]

Hi,

I want to use a ws as a template (used many times). What is the code
to create a copy of the template ws and rename it using a string
variable?

The template has a fair bit of formatting, so I presume this method is
better than creating new sheets and having the code format each new
sheet?

kind regards,
Matt

 

[Jim Cone]

Use the Sheets.Add method...
Sheets.Add before:=Sheets(1), Count:=1,Type:= _
"C:\Documents and Settings\MJKelly\Application Data\Microsoft\Templates\Timecard.xlt"
--
Jim Cone
Portland, Oregon USA



"MJKelly"
wrote in message
Hi,
I want to use a ws as a template (used many times). What is the code
to create a copy of the template ws and rename it using a string
variable?
The template has a fair bit of formatting, so I presume this method is
better than creating new sheets and having the code format each new
sheet?
kind regards,
Matt

 

[Gord Dibben]

Try this from Dave Peterson.

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