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