Name Worksheet Tabs Automatically Based On Cell

[BOONWEEBO]

Seeking to name 40 worksheets in a workbook automatically, based on Name in B1 in each worksheet. Data and worksheet names will change monthly.

I am new to this so I will need explicit instructions. Ty

[Dave Hawley]

Hi BOONWEEBO

The code below will name each Worksheet in the Workbook based on the content of cell B1 of each Worksheet each time the Workbook is opened. To use the code, right click on the Excel icon, top left next to File and select View Code. In here paste the code below.

Private Sub Workbook_Open() 
    Dim wSheet As Worksheet 
    On Error Resume Next 
    For Each wSheet In Me.Worksheets 
        wSheet.Name = wSheet.Range("B1") 
    Next wSheet 
    On Error Goto 0 
End Sub 

If certain Worksheets should be exclude let me know their names (or their Index number) and I will modify the code. There is also no error check to ensure cell B1 does not house characters that cannot be used, let me know if you would like that added.

[BOONWEEBO]

TY works well on the initial data. However when the data changes, and where is no reference in B1, one tab name changes to "0" and the others remain named after the previous data.

ie. 40 sheets all properly named with first data
The new data has only 35 entries in B1.
These new names populate correctly,
Sheet 36 name is changed to "0"
The remaining sheets retain the original data names.

How do I then rename these last 4 sheets? Number or Alpha progression is fine. TY K

[BOONWEEBO]

#3 21 Hours Ago
BOONWEEBO
Junior Member MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Average


Join Date: 8th September 2004
Posts: 2

TY works well on the initial data. However when the data changes, and where is no reference in B1, one tab name changes to "0" and the others remain named after the previous data.

ie. 40 sheets all properly named with first data
The new data has only 35 entries in B1.
These new names populate correctly,
Sheet 36 name is changed to "0"
The remaining sheets retain the original data names.

How do I then rename these last 4 sheets? Number or Alpha progression is fine. TY K

[Dave Hawley]

How do I then rename these last 4 sheets? Number or Alpha progression is fine

What do the other sheets get named like? I need to know this if you would like Number or Alpha progression.

[BOONWEEBO]

Ideally there would be no names in B1, but it is my understanding that tabs need to have text in them. If the cell is blank or "0" the tab names do not change. TY again for your help

[iwrk4dedpr]

Ty,

I've added to Dave's code. Check out the following. If the range B1 is blank then it renames the sheet with "Sheet" and it index number.

Private Sub Workbook_Open() 
    Dim wSheet As Worksheet 
    On Error Resume Next 
    For Each wSheet In Me.Worksheets 
        If wSheet.Range("B1") = "" Then 
            wSheet.Name = "Sheet" & wSheet.Index 
        Else 
            wSheet.Name = Format(wSheet.Range("B1"), "mmm dd, yyyy") 
        End If 
    Next wSheet 
    On Error Goto 0 
End Sub 

Regards,
Barry

[BOONWEEBO]

That does the trick! I have run it changing the B1 data several times and everything renames properly now.

TY Barry and Dave

[alexsheehan]

Thanks for the tips, this is great and works well - however, how do you omit specific Worksheets? Thanks very much for the help, I appreciate this is an old post but I have just recently had the need to do this and need to know how specific sheets can be omitted from the code.

Many thanks

Alex

[iwrk4dedpr]

What is the criteria for omitting sheets? Is it the name? Is it the position?

If it's the name of the worksheet then

Private Sub Workbook_Open() 
    Dim wSheet As Worksheet 
    On Error Resume Next 
    For Each wSheet In Me.Worksheets 
        [COLOR="red"]if wSheet.Name = "SomeName" then[/COLOR] 
        If wSheet.Range("B1") = "" Then 
            wSheet.Name = "Sheet" & wSheet.Index 
        Else 
            wSheet.Name = Format(wSheet.Range("B1"), "mmm dd, yyyy") 
        End If 
        [COLOR="red"]End If[/COLOR] 
    Next wSheet 
    On Error Goto 0 
End Sub