tabs

[duckie]

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

 

[JLGWhiz]

The code you posted allows the user to put a name in cell A1 of
the active sheet and then activate the code to assign that name
to that sheet.

If you want the names in column C to be used for assignment to
the work sheets, you need to specify:
1. Do the 20 sheets already exist or will new sheets be required?
2. Will every sheet in the workbook be named from the names in
Column C or are you using additional sheets for other data?
3. If you are using other sheets, how many and do you want them
to be the first sheet(s) on the left or last on the right.

The sheets will be named in the same order as the names are
listed in Column C from top to bottom.

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[duckie]

The code you posted allows the user to put a name in cell A1 of
the active sheet and then activate the code to assign that name
to that sheet.

If you want the names in column C to be used for assignment to
the work sheets, you need to specify:
1.  Do the 20 sheets already exist or will new sheets be required?
2.  Will every sheet in the workbook be named from the names in
    Column C or are you using additional sheets for other data?
3.  If you are using other sheets, how many and do you want them
    to be the first sheet(s) on the left or last on the right.

The sheets will be named in the same order as the names are
listed in Column C from top to bottom.







- Show quoted text -

i already have the workbook and all the names are only on one sheet
called the directory

 

[duckie]

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







- Show quoted text -

i am very new to this type of work in excel

 

[JLGWhiz]

I modified this to avoid over writing your sheet named "Directory" other
instructions are included below.

Sub nmsh()
Dim lstRw, x, i As Long
lstRw = Cell(Rows.Count, 3).End(xlUp).Row
x = 2
For i = 4 To 23
If Sheet.Name <> "Directory"
Sheets(x).Name = Cells(i, 3).Value
x = x + 1
End If
Next
End Sub

To use this code, while holding the Alt key, press the F11 key [Alt + F11].
This opens the Visual Basic Editor window. If the large window is dark,
click Insert on the menu bar of the VBE window, then click module. That
should display a lighter screen in the code window. Paste the code above
into the window. Then activate the Excel window and click Tools>Macro>Macros
which will display a dialog box. If "nmsh" appears in the small window at
the top of the dialog box, then click Options. If it does not appear in the
small window, find it in the larger window and click it, it should then show
in the smaller window, then click Options. Another dialog box will appear
with a very small input window and a caption that says Ctl +.
Type a keyboard letter into that small box then click OK. It is not
necessary to enter anything in the Description box, unless you might think
you will forget what the macro is for. If you enter anything in the
description box, do it before clicking OK. You can then use the Ctl plus the
letter you chose to start the macro from the keyboard instead of having to
open the macro dialog box each time to run your macro.

 

[duckie]

I modified this to avoid over writing your sheet named "Directory"  other
instructions are included below.

Sub nmsh()
                 Dim lstRw, x, i As Long
        lstRw = Cell(Rows.Count, 3).End(xlUp).Row
        x = 2
        For i = 4 To 23
                       If Sheet.Name <> "Directory"
                Sheets(x).Name = Cells(i, 3).Value
                x = x + 1
                       End If
        Next
 End Sub        

To use this code, while holding the Alt key, press the F11 key [Alt + F11]..  
This opens the Visual Basic Editor window.  If the large window is dark,
click Insert on the menu bar of the VBE window, then click module.  That
should display a lighter screen in the code window.  Paste the code above
into the window.  Then activate the Excel window and click Tools>Macro>Macros
which will display a dialog box.  If "nmsh" appears in the small window at
the top of the dialog box, then click Options.  If it does not appear inthe
small window, find it in the larger window and click it, it should then show
in the smaller window, then click Options.  Another dialog box will appear
with a very small input window and a caption that says Ctl +.
Type a keyboard letter into that small box then click OK.  It is not
necessary to enter anything in the Description box, unless you might think
you will forget what the macro is for.  If you enter anything in the
description box, do it before clicking OK.  You can then use the Ctl plus the
letter you chose to start the macro from the keyboard instead of having to
open the macro dialog box each time to run your macro.

i am very new to this type of work in excel- Hide quoted text -

- Show quoted text -

i am still having problems i was wondering if i could email the
workbook to you