Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic tab naming
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 doesnt 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 -- Rex Munn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic tab naming
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 On Error GoTo 0 End Sub "Rex" wrote: 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 doesnt 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 -- Rex Munn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic tab naming
Thanks. That helped.
-- Rex Munn "JLGWhiz" wrote: 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 On Error GoTo 0 End Sub "Rex" wrote: 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 doesnt 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 -- Rex Munn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Sheet Naming | Excel Programming | |||
Automatic Sheet Naming | Excel Programming | |||
Automatic naming Worksheet tabs | Excel Programming | |||
Automatic naming Worksheet tabs | Excel Programming | |||
Automatic sheet naming | Excel Programming |