I think it would be easier to just copy the template worksheet.

Option Explicit
Sub Create_Agent_Sheets()
Dim myCell As Range
Dim myRange As Range
Dim TemplWks As Worksheet
Dim TemplVis As Long

Set TemplWks = Worksheets("Template")
TemplVis = TemplWks.Visible
TemplWks.Visible = xlSheetVisible

With Worksheets("Agency Info")
Set myRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRange.Cells
TemplWks.Copy _
after:=Sheets(Sheets.Count)
On Error Resume Next
With ActiveSheet
.Name = myCell.Value '.text may be better
If Err.Number < 0 Then
MsgBox "Please rename: " & .Name & vbLf _
& myCell.Value & " wasn't valid"
Err.Clear
End If
End With
On Error GoTo 0
Next myCell

TemplWks.Visible = TemplVis

End Sub

If any of those cells could contain a number or date, you may want to use .text
instead of .value (for the .name statement).

or even format it yourself (for dates):

..Name = format(myCell.Value, "yyyymmdd")
(since /'s aren't legal in sheet names)

MLewis123 wrote:

Hello,

I have an easy question for someone.

I am trying to automatically add a workbook page and copy and master
template. I got the formala to make adding the sheet and naming the workbook,
but cannot figure out what it takes to copy the template to each of the
workbooks. Any thoughts. Here is the code I am currently using. Let's say the
template worksheet is labeled "template"

Sub Create_Agent_Sheets()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Agency Info").Range("a2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
Next MyCell
End Sub

--

Dave Peterson