Automatic tab naming

[Rex]

Using Excel 2003

I have a workbook with approx. 70 worksheets. I need to automatically name
only 50 of the worksheet tabs with cell references from the 1st worksheet.

On the first worksheet, cells B50 through B100 contain the names for the
other 50 tabs. Each of the 50 worksheets reference a cell from the first
worksheet in cell D4 (the 1st one references (Tab1)B50, 2nd = (Tab1)B51, 3rd
= (Tab1)B52, etc.) I inserted the following code into the workbook:

Private Sub Workbook_Open()
Dim wks As Worksheet

On Error Resume Next
For Each wks In Worksheets
wks.Name = wks.Range("D4").Value
Next wks
On Error GoTo 0
End Sub

Here are some of the issues I am having that I need help with:
1. When I add names in the first worksheet and then save the file and
re-open, it changes the names of the 50 worksheets. It doesn’t do it until
after I reopen and enable macros. I can live with this if I have to but if
there is a way to get the code to fire right away I would like to know.
2. This code obviously renames all worksheet tab names with their cell D4
contents and I only want the specific 50 tabs to rename themselves.
3. If I only enter text or numbers in 10 of the cells on the first tab, the
11th worksheet is named “0â€.
4. If I delete some of the names from the first tab, save the file and
reopen it, the worksheets that should now have no name keep the name they
originally had.

I appreciate any help you can give…..
Thanks

 

[JLGWhiz]

The modifications below might help with the 0 Name
and the naming of sheets in excess of 50. In regard
to the sheets having their original names after they
had been changed to no name, Excel will automatically
name them if you don't. It is a built in default.


Private Sub Workbook_Open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In Worksheets
If wks.Range("D4").Value <> "" Then
wks.Name = wks.Range("D4").Value
End If
If wks.Index > 50 Then Exit For
Next wks

 

[Rex]

Thanks. That helped.
--
Rex Munn

The modifications below might help with the 0 Name
and the naming of sheets in excess of 50. In regard
to the sheets having their original names after they
had been changed to no name, Excel will automatically
name them if you don't. It is a built in default.


Private Sub Workbook_Open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In Worksheets
If wks.Range("D4").Value <> "" Then
wks.Name = wks.Range("D4").Value
End If
If wks.Index > 50 Then Exit For
Next wks