Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 MLewis123 wrote: 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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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" wrote: 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 MLewis123 wrote: 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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
One way is to check the value before you do anything with it:
For Each myCell In myRange.Cells if mycell.value = "" then 'do nothing else 'do the work TemplWks.Copy _ after:=Sheets(Sheets.Count) .... End With On Error GoTo 0 end if Next myCell 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 wrote: 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" wrote: 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 MLewis123 wrote: 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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
Also, for some reason it is adding 10 tabs called "templates"
"MLewis123" wrote: 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" wrote: 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 MLewis123 wrote: 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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
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 wrote: Also, for some reason it is adding 10 tabs called "templates" "MLewis123" wrote: 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" wrote: 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 MLewis123 wrote: 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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
Everything is working like a charm!!!! Awesome thanks for the help
"Dave Peterson" wrote: 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 wrote: Also, for some reason it is adding 10 tabs called "templates" "MLewis123" wrote: 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" wrote: 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 MLewis123 wrote: 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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Adding a Worksheet
Woohoo!!!
MLewis123 wrote: Everything is working like a charm!!!! Awesome thanks for the help "Dave Peterson" wrote: 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 wrote: Also, for some reason it is adding 10 tabs called "templates" "MLewis123" wrote: 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" wrote: 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 MLewis123 wrote: 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 "Dave Peterson" wrote: 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): With ActiveSheet .range("A2").value = mycell.offset(0,1).value 'column B .range("b2").value = mycell.offset(0,2).value 'column C .range("c2").value = mycell.offset(0,5).value 'column F 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 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!) MLewis123 wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically adding an Add-in | Excel Programming | |||
Adding an AP to a worksheet automatically | Excel Worksheet Functions | |||
Adding Data Automatically | Excel Programming | |||
Why are my formulas not adding up automatically | Excel Worksheet Functions | |||
Automatically adding cells from one worksheet to another | Excel Worksheet Functions |