Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
Hello All:
I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
after the line
Sheets.Add.Name = cell.Value add this Sheets("template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1") -- Cheers Nigel "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
Thanks.
I have changed the code as you have suggested to: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value Sheets("Template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1") End If Next End Sub I get an --Run-Time error"9" Subscript out of range. I am trying diffrent variations of code but so far am not successfull. Should A1 be A2 Ardy Nigel wrote: after the line Sheets.Add.Name = cell.Value add this Sheets("Template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1") -- Cheers Nigel "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
hi Ardy
Is the name of your template sheet called Template, the name on the Tab ? -- Cheers Nigel "Ardy" wrote in message ups.com... Thanks. I have changed the code as you have suggested to: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value Sheets("Template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1") End If Next End Sub I get an --Run-Time error"9" Subscript out of range. I am trying diffrent variations of code but so far am not successfull. Should A1 be A2 Ardy Nigel wrote: after the line Sheets.Add.Name = cell.Value add this Sheets("Template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1") -- Cheers Nigel "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
Yes
Nigel wrote: hi Ardy Is the name of your template sheet called Template, the name on the Tab ? -- Cheers Nigel "Ardy" wrote in message ups.com... Thanks. I have changed the code as you have suggested to: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value Sheets("Template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1") End If Next End Sub I get an --Run-Time error"9" Subscript out of range. I am trying diffrent variations of code but so far am not successfull. Should A1 be A2 Ardy Nigel wrote: after the line Sheets.Add.Name = cell.Value add this Sheets("Template").Cells.Copy Destination:=Sheets(cell.Value).Range("A1") -- Cheers Nigel "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
First of all I want to thank you both for helping me on this. My wife
who is a K9 teacher is also very appreciative of this. Bob, Nigel I have created a second Command button to preserve my first code. I get the same error on Bob's version of the code. When it says out of range dose this mean range (Colum A is grater than it can handle) I only have 3 names for the sake of testing. It potentially could go to 40. Not to over extend my welcome I also am trying to link the names(in the Roster Tab) to their respected tabs (worksheets)either after or during the creation. This is for the Teachers to easily navigate to each student tab. I have already have code in each tab to navigate back to the Roster. --------------Latest Code --------------------------------- Private Sub CommandButton2_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub --------------------------------------------------------------------- Bob Phillips wrote: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
I tested it and it worked okay for me.
Where do you get the error? We'll get the create to work first, then worry about the navigating. BTW, what is a K9 teacher? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... First of all I want to thank you both for helping me on this. My wife who is a K9 teacher is also very appreciative of this. Bob, Nigel I have created a second Command button to preserve my first code. I get the same error on Bob's version of the code. When it says out of range dose this mean range (Colum A is grater than it can handle) I only have 3 names for the sake of testing. It potentially could go to 40. Not to over extend my welcome I also am trying to link the names(in the Roster Tab) to their respected tabs (worksheets)either after or during the creation. This is for the Teachers to easily navigate to each student tab. I have already have code in each tab to navigate back to the Roster. --------------Latest Code --------------------------------- Private Sub CommandButton2_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub --------------------------------------------------------------------- Bob Phillips wrote: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
I get the error in line:
Sheets("Template").Copy after:=Worksheets(Worksheets.Count) I have some images that might help you see what I see. At http://www.pesare-darya.com/error.htm Eventually when I get it working all names will be under column A. K9 is the term used for teachers that are certified by state to teach in public schools grade Kinder to 9th grade, basically your elementary school all the way to end of middle school. My wife is a 1st grade teacher. It sound like dog trainer K9 some time I bug her abt that.....LOL. Ardy Bob Phillips wrote: I tested it and it worked okay for me. Where do you get the error? We'll get the create to work first, then worry about the navigating. BTW, what is a K9 teacher? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... First of all I want to thank you both for helping me on this. My wife who is a K9 teacher is also very appreciative of this. Bob, Nigel I have created a second Command button to preserve my first code. I get the same error on Bob's version of the code. When it says out of range dose this mean range (Colum A is grater than it can handle) I only have 3 names for the sake of testing. It potentially could go to 40. Not to over extend my welcome I also am trying to link the names(in the Roster Tab) to their respected tabs (worksheets)either after or during the creation. This is for the Teachers to easily navigate to each student tab. I have already have code in each tab to navigate back to the Roster. --------------Latest Code --------------------------------- Private Sub CommandButton2_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub --------------------------------------------------------------------- Bob Phillips wrote: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Worksheet From Values in Existing Cells Using Existing Worksheet as Template.
This continues to be the problem, the only subscript is the sheet
"Template", you have confirmed that it exists and has this name. My earlier code works OK in my environment and Bob's code uses the copy sheet after method relies on the sheet both existing and being named Template. You had said it worked at one stage, I recall. Can you check after the error has arisen that the sheet Template is still there? Also one other thought change the test for an empty cell in the range (with names) If Not IsEmpty(cell) Then to this If Len(Trim(cell)) 0 then -- Cheers Nigel "Ardy" wrote in message oups.com... I get the error in line: Sheets("Template").Copy after:=Worksheets(Worksheets.Count) I have some images that might help you see what I see. At http://www.pesare-darya.com/error.htm Eventually when I get it working all names will be under column A. K9 is the term used for teachers that are certified by state to teach in public schools grade Kinder to 9th grade, basically your elementary school all the way to end of middle school. My wife is a 1st grade teacher. It sound like dog trainer K9 some time I bug her abt that.....LOL. Ardy Bob Phillips wrote: I tested it and it worked okay for me. Where do you get the error? We'll get the create to work first, then worry about the navigating. BTW, what is a K9 teacher? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... First of all I want to thank you both for helping me on this. My wife who is a K9 teacher is also very appreciative of this. Bob, Nigel I have created a second Command button to preserve my first code. I get the same error on Bob's version of the code. When it says out of range dose this mean range (Colum A is grater than it can handle) I only have 3 names for the sake of testing. It potentially could go to 40. Not to over extend my welcome I also am trying to link the names(in the Roster Tab) to their respected tabs (worksheets)either after or during the creation. This is for the Teachers to easily navigate to each student tab. I have already have code in each tab to navigate back to the Roster. --------------Latest Code --------------------------------- Private Sub CommandButton2_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub --------------------------------------------------------------------- Bob Phillips wrote: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets("Template").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If Next End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Ardy" wrote in message oups.com... Hello All: I have been trying to do the following and haven't been successful. I have a workbook(Excel file) that has two worksheet one is called Roster and the other is called template(a pre-formated worksheet). In the Roster I have the names of the students. In one Colum A starting from cell A2. I have created a button that will hopefully do the following. 1) Reads the values in Colum A (starting from A2). 2) Create worksheets using Template worksheet and naming it the values stored in previous step. Please know that I am not a programmer but do my best to peace mill What I find. I have found, Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp) Set Rng = WS.Range("A2", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Sheets.Add.Name = cell.Value End If Next End Sub This makes the worksheets with the name of the values, but I can't quite get it to use the template for copying. Any help on this is greatly appreciated. Ardy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I apply a template to one worksheet in existing workbook? | Excel Discussion (Misc queries) | |||
is it possible to apply template to a existing worksheet | Excel Worksheet Functions | |||
'Template' within a worksheet - change existing worksheets | Excel Programming | |||
apply a template to existing worksheet | Excel Discussion (Misc queries) | |||
How do I conditionally build worksheet from existing worksheet? | Excel Discussion (Misc queries) |