Auto naming Tabs in a Workbook

[Johnny]

Is there a way to automatically name/rename a tab based on the cell contents
in a worksheet? Thanks.

 

[Allllen]

Johnny,

Here is a start for you. It renames based on the values of column A.
So, if you start a blank workbook with 3 sheets and write in the cells
A1 = "pig"
A2 = "horse"
A3 = "cow"
then run this program, you will end up with 3 sheets called pig, horse and
cow.


Sub namesheets()

Dim x As Integer

For x = 1 To Sheets.Count
Sheets(x).Name = Cells(x, 1).Value
Next x

End Sub

 

[Johnny]

Thanks for your reply Alan. In my workbook, I have 4 workseets in which I
do not want to rename the tabs. Let me be a little more discriptive on what
I need. I want to be name each tab based on the cell contains in particular
that particular worksheet. For examples, if cell A1 in worksheet 1 is "Bob"
then name the tab Bob. Can you help? Thanks.

 

[Don Guillett]

Lots of different ways such as
for each ws in worksheets
if ws.name<>"Bill" and ws.name<>"sam" then ws.name=range("a1")
next

 

[Johnny]

I meant to say that I did not want to rename all of the tabs in the workbook.
I do however wish to auto re-name only a select view base on the cells
contents in that tab.

 

[Gord Dibben]

Johnny

You can paste this code into the sheet module of those you wish to rename
automatically.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Me.Name = Range("A1").Text
CleanUp:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Change the value of A1 and sheet name will follow.


Gord Dibben MS Excel MVP

I meant to say that I did not want to rename all of the tabs in the workbook.
I do however wish to auto re-name only a select view base on the cells
contents in that tab.

Gord Dibben MS Excel MVP