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
|