Automatically Adding a Worksheet

[MLewis123]

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]

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]

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]

Each new sheet gets the same values from the Data tab and they go into A2, B2,
....???
.range("A2").value = worksheets("Data").range("x99").value
.range("b2").value = worksheets("Data").range("z99").value

 

[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

 

[MLewis123]

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.

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]

Yep. It makes more sense, but I'm gonna give you more work to do.

I wouldn't rely on the first entry in the Agency Info worksheet to be the first
entry on the Data worksheet. Instead I'd make sure that each row in the data
sheet has the corresponding name for that agency:

Col A Col B ...
MLewisAgency 14.32%
MarkAgency 4.32%
....

It would be a problem (for me) to keep things in the correct sequence. And if I
sorted one of the sheets differently than the other, then all my data would be
mismatched.

In fact, is there a reason that stops you from using the Data worksheet instead
of the agency info sheet? You could have all the data laid out nicely in one
location?

But if there is a reason, then in your Agency info, you could use formulas to
retrieve the values you want from the data sheet.

In B2 (first agency id in A2 and headers in row 1), you could use:
=vlookup($a2,data!$a:$e,2,false)
and the same for the other columns to be retrieved.

I would expect each agency to have data, but if there's a mistyped name, you'll
see an error #n/a. (Which is another good reason to put all your data in one
sheet.

Then format your template sheet the way you like (cells that show percents
should be formatted as percents, dates as dates, etc).

And the code will just pick up those adjacent values and plop them into the new
sheets based on the template (and the associated name):

And if you decide to use the Data worksheet, you can change this line:
With Worksheets("Agency Info")
to use the correct worksheet name.

If the agency names are in a different column, you can change these lines:
Set myRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
and
.range("A2").value = mycell.offset(0,1).value 'column B
The .offset(x,y) means x rows (down for positive, up for negative) and y columns
(right for positive and left for negative) to pick up the info from the columns
you want.

What do you think?

But whatever you do, don't just rely on the order of the data in each
worksheet. (Insert a new row in the wrong position and kablewie!)

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.

 

[MLewis123]

It looks like we could talk about this for days....

Anyway there is a reason I have to use a different sheet, not that I want
to. I am using a formula from a User Form for a survey that for some reason
if there is any cod or anything anywhere on the line it is supposed to write
to regardless if it is AE250, it moves to the next open line. I cannot
figure that out. Here is the code: Each of the text boxes are the survey
questions.

However, it is dropping the code as you stated, but it just requires me to
have another sheet. Any thoughts on this? I like your idea and it would
certainly simplify my life. ))

RowCount = Worksheets("Agency Info").Range("a1").CurrentRegion.Rows.Count
With Worksheets("Agency Info").Range("a1")
.Offset(RowCount, 0).Value = Me.TextBox1.Value
.Offset(RowCount, 1).Value = Me.TextBox2.Value
.Offset(RowCount, 2).Value = Me.TextBox4.Value
.Offset(RowCount, 3).Value = Me.TextBox5.Value
.Offset(RowCount, 4).Value = Me.TextBox6.Value
.Offset(RowCount, 5).Value = Me.TextBox19.Value
.Offset(RowCount, 6).Value = Me.TextBox8.Value
.Offset(RowCount, 7).Value = Me.TextBox17.Value
.Offset(RowCount, 8).Value = Me.TextBox9.Value
.Offset(RowCount, 9).Value = Me.TextBox11.Value
.Offset(RowCount, 10).Value = Me.TextBox12.Value
.Offset(RowCount, 11).Value = Me.TextBox13.Value
.Offset(RowCount, 12).Value = Me.TextBox14.Value
.Offset(RowCount, 13).Value = Me.TextBox15.Value
.Offset(RowCount, 14).Value = Me.TextBox16.Value
.Offset(RowCount, 15).Value = Me.TextBox10.Value
.Offset(RowCount, 16).Value = Me.TextBox18.Value
End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = ""
End If
Next ctl

Yep. It makes more sense, but I'm gonna give you more work to do.

I wouldn't rely on the first entry in the Agency Info worksheet to be the first
entry on the Data worksheet. Instead I'd make sure that each row in the data
sheet has the corresponding name for that agency:

Col A Col B ...
MLewisAgency 14.32%
MarkAgency 4.32%
....

It would be a problem (for me) to keep things in the correct sequence. And if I
sorted one of the sheets differently than the other, then all my data would be
mismatched.

In fact, is there a reason that stops you from using the Data worksheet instead
of the agency info sheet? You could have all the data laid out nicely in one
location?

But if there is a reason, then in your Agency info, you could use formulas to
retrieve the values you want from the data sheet.

In B2 (first agency id in A2 and headers in row 1), you could use:
=vlookup($a2,data!$a:$e,2,false)
and the same for the other columns to be retrieved.

I would expect each agency to have data, but if there's a mistyped name, you'll
see an error #n/a. (Which is another good reason to put all your data in one
sheet.

Then format your template sheet the way you like (cells that show percents
should be formatted as percents, dates as dates, etc).

And the code will just pick up those adjacent values and plop them into the new
sheets based on the template (and the associated name):

And if you decide to use the Data worksheet, you can change this line:
With Worksheets("Agency Info")
to use the correct worksheet name.

If the agency names are in a different column, you can change these lines:
Set myRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
and
.range("A2").value = mycell.offset(0,1).value 'column B
The .offset(x,y) means x rows (down for positive, up for negative) and y columns
(right for positive and left for negative) to pick up the info from the columns
you want.

What do you think?

But whatever you do, don't just rely on the order of the data in each
worksheet. (Insert a new row in the wrong position and kablewie!)

 

[Dave Peterson]

First, .currentregion will only use the contiguous area of used cells.

Select A1 and hit ctrl-shift-8 (ctrl-*). This selects the current region.
AE250 may not be in that current region and it won't be considered in that
rowcount.

So if you wanted, you could put any info you want in a column that is separted
from your data (the current region of A1) by at least one empty column.

I'm not sure what your question is though. If you want to include that AE250
cell, then you either have to make it part of A1's current region (remove any
empty columns between the last used column and column AE) or fill some cells in
those columns with stuff that would extend that current region.

Personally, I've never been a fan of using .currentregion or even
..specialcells(xlCellTypeLastCell). Both may not work the way I require.

But there are other ways to find that last used row.

If I know my data, then I know what columns are always used if the row is
used--maybe it's a unique key (Agent name or customer id or some sales order
number or a serial number or ...).

Then I'd use something like.

Dim RowCount as long
with worksheets("My sheet here"
rowcount = .cells(.rows.count,"X").end(xlup).row
end with
(I used column X for that always used column if the row is used.)

Or you could use Ron de Bruin's technique.
http://www.rondebruin.nl/last.htm

It looks like we could talk about this for days....

Anyway there is a reason I have to use a different sheet, not that I want
to. I am using a formula from a User Form for a survey that for some reason
if there is any cod or anything anywhere on the line it is supposed to write
to regardless if it is AE250, it moves to the next open line. I cannot
figure that out. Here is the code: Each of the text boxes are the survey
questions.

However, it is dropping the code as you stated, but it just requires me to
have another sheet. Any thoughts on this? I like your idea and it would
certainly simplify my life. ))

RowCount = Worksheets("Agency Info").Range("a1").CurrentRegion.Rows.Count
With Worksheets("Agency Info").Range("a1")
.Offset(RowCount, 0).Value = Me.TextBox1.Value
.Offset(RowCount, 1).Value = Me.TextBox2.Value
.Offset(RowCount, 2).Value = Me.TextBox4.Value
.Offset(RowCount, 3).Value = Me.TextBox5.Value
.Offset(RowCount, 4).Value = Me.TextBox6.Value
.Offset(RowCount, 5).Value = Me.TextBox19.Value
.Offset(RowCount, 6).Value = Me.TextBox8.Value
.Offset(RowCount, 7).Value = Me.TextBox17.Value
.Offset(RowCount, 8).Value = Me.TextBox9.Value
.Offset(RowCount, 9).Value = Me.TextBox11.Value
.Offset(RowCount, 10).Value = Me.TextBox12.Value
.Offset(RowCount, 11).Value = Me.TextBox13.Value
.Offset(RowCount, 12).Value = Me.TextBox14.Value
.Offset(RowCount, 13).Value = Me.TextBox15.Value
.Offset(RowCount, 14).Value = Me.TextBox16.Value
.Offset(RowCount, 15).Value = Me.TextBox10.Value
.Offset(RowCount, 16).Value = Me.TextBox18.Value
End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = ""
End If
Next ctl

 

[MLewis123]

Believe it or not, I pretty much have it now.....one last thing. The range
function....Set myRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) is
actually adding up blank cells or I should say cells that have a formula but
nothing in as text. Any ideas how to stop this?

Thank you so much for the help!!!!!!!! You rock!!!!

 

[Dave Peterson]

One way is to check the value before you do anything with it:
if mycell.value = "" then
'do nothing
else
'do the work
If you wanted, you could use the code at Ron's site and use .find(). He
actually has a caveat at the bottom of that page describing your situation.

 

[MLewis123]

Also, for some reason it is adding 10 tabs called "templates"

 

[Dave Peterson]

If you're processing those cells that look empty, then you should be getting
messages that say that these are invalid names and you have to rename them
yourself.

But the response to the previous pose will stop the blank cells from causing the
trouble.

(You can still have the trouble if you have names in that range that are not
legal sheet names.)

 

[MLewis123]

Everything is working like a charm!!!! Awesome thanks for the help

 

[Dave Peterson]

Woohoo!!!

Everything is working like a charm!!!! Awesome thanks for the help