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