The code I gave you goes in the standard module, NOT the sheet module.

"JLGWhiz" wrote:

If you only have twenty sheets in the workbook, here is the code.
If if gives an error, post back.

Sub nmsh()
Dim lstRw, x, i As Long
lstRw = Cell(Rows.Count, 3).End(xlUp).Row
x = 1
For i = 4 To 23
SHeets(x).Name = Cells(i, 3).Value
x = x + 1
Next
End Sub

"duckie" wrote:

I am still having problems with tab naming
I have a sheet named directory and in column c from c4 to c23 I have
names of players which I want to be named on tabs on 20 work sheets
I was given the following to use but I can not get it to work

Could someone please help me as the football season is ready to start



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing
Then
sSheetName = Sheets("Sheet1").Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub