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