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

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

Dave,

Thank you so much it worked perfectly!!!!!!....now, I need to do one more
thing to the macro that maybe you can help me with. I did not want to
confuse the issue when posting the first.

I the first workbook "agency info" that is creating the tabs, template, and
naming the tabs." Now, I have a data tab that houses other information that
I would like to fill the templates with. For example, A2 would be score from
the data tab, B2 would be the retention, etc. I know it is hard to create
the macro since you do not have the exact cells, but if you can give me the
shell that would be great.

"Dave Peterson" wrote:

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

Each new sheet gets the same values from the Data tab and they go into A2, B2,
....???

With ActiveSheet
.range("A2").value = worksheets("Data").range("x99").value
.range("b2").value = worksheets("Data").range("z99").value
.Name = myCell.Value '.text may be better
If Err.Number < 0 Then



MLewis123 wrote:

Dave,

Thank you so much it worked perfectly!!!!!!....now, I need to do one more
thing to the macro that maybe you can help me with. I did not want to
confuse the issue when posting the first.

I the first workbook "agency info" that is creating the tabs, template, and
naming the tabs." Now, I have a data tab that houses other information that
I would like to fill the templates with. For example, A2 would be score from
the data tab, B2 would be the retention, etc. I know it is hard to create
the macro since you do not have the exact cells, but if you can give me the
shell that would be great.

"Dave Peterson" wrote:

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


--

Dave Peterson

I should have included more of the snippet.

For Each myCell In myRange.Cells
TemplWks.Copy _
after:=Sheets(Sheets.Count)

With ActiveSheet
.range("A2").value = worksheets("Data").range("x99").value
.range("b2").value = worksheets("Data").range("z99").value
On Error Resume Next
.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
On Error GoTo 0
End With
Next myCell

Dave Peterson wrote:

Each new sheet gets the same values from the Data tab and they go into A2, B2,
...???

With ActiveSheet
.range("A2").value = worksheets("Data").range("x99").value
.range("b2").value = worksheets("Data").range("z99").value
.Name = myCell.Value '.text may be better
If Err.Number < 0 Then

MLewis123 wrote:

Dave,

Thank you so much it worked perfectly!!!!!!....now, I need to do one more
thing to the macro that maybe you can help me with. I did not want to
confuse the issue when posting the first.

I the first workbook "agency info" that is creating the tabs, template, and
naming the tabs." Now, I have a data tab that houses other information that
I would like to fill the templates with. For example, A2 would be score from
the data tab, B2 would be the retention, etc. I know it is hard to create
the macro since you do not have the exact cells, but if you can give me the
shell that would be great.

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson

Dave,
Thanks for getting me on the right track...I am sorry I keep asking
questions.

The way it is setup right now, the macro copies one line "x99" to the
template. However, each tab has a different number to it. Let me try to
explain.

The macro to create the tabs is correct.
The macro to copy the template tab is correct.

Now, there is the data tab that is kind of setup like this.

b3 = retention %, but this is for the first agency only; b4 = retention %,
for the second agency only, and so forth. Each line needs to show up in the
same spot on the template, but needs to bring the data from a different point
on the data sheet. c3 = premium $, again for first agency/tab only; c4 =
premium $ for second agency and so forth. Does this make a little more sense?

You have gotten me further than anyone else.....so I greatly appreciate your
help.

"Dave Peterson" wrote:

I should have included more of the snippet.

For Each myCell In myRange.Cells
TemplWks.Copy _
after:=Sheets(Sheets.Count)

With ActiveSheet
.range("A2").value = worksheets("Data").range("x99").value
.range("b2").value = worksheets("Data").range("z99").value
On Error Resume Next
.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
On Error GoTo 0
End With
Next myCell

Dave Peterson wrote:

Each new sheet gets the same values from the Data tab and they go into A2, B2,
...???

With ActiveSheet
.range("A2").value = worksheets("Data").range("x99").value
.range("b2").value = worksheets("Data").range("z99").value
.Name = myCell.Value '.text may be better
If Err.Number < 0 Then

MLewis123 wrote:

Dave,

Thank you so much it worked perfectly!!!!!!....now, I need to do one more
thing to the macro that maybe you can help me with. I did not want to
confuse the issue when posting the first.

I the first workbook "agency info" that is creating the tabs, template, and
naming the tabs." Now, I have a data tab that houses other information that
I would like to fill the templates with. For example, A2 would be score from
the data tab, B2 would be the retention, etc. I know it is hard to create
the macro since you do not have the exact cells, but if you can give me the
shell that would be great.

"Dave Peterson" wrote:

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


--

Dave Peterson

--

Dave Peterson