Rename Tabs

[-emma-]

Hi,

I am using a spread sheet which contains a "CoverSheet" tab naming 6 different people. Cells A2 - A7 hold names of people. The spread sheet contains another 6 tabs each labled the same as cells A2 - A7. Unfortunatly I change the names in cells A2 - A7 on a regular basis and would like the tab names to automaticly change too.

Any ideas anyone? Any suggestions would be helpful.

Thanks,

Emma

 

[HalfAce]

Hello Emma, welcome to the board.
You can use a bit of vba for that.
Insert a standard module and put this into it.
Public OldName As String

Put this into the sheet module for CoverSheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then OldName = "": Exit Sub
If Not Intersect(Target, Range("A2:A7")) Is Nothing Then _
  OldName = Target.Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:A7")) Is Nothing Then Exit Sub

If OldName <> "" And Target.Value <> OldName Then
  Dim Sheet As Worksheet
  For Each Sheet In Worksheets
    On Error GoTo ErrTrp
    If Sheet.Name = OldName Then Sheet.Name = Target.Value
  Next Sheet
End If
Exit Sub

ErrTrp:
If Err.Number = 1004 Then
  MsgBox "There is already a sheet named '" & Target.Value & "'." & Chr(10) & _
         "Please choose a name not currently being used."
  Target.Value = OldName
Else
  MsgBox Err.Number & ": " & Err.Description
End If
         
End Sub

 

[-emma-]

It works great...

Thank You

Emma x

 

[HalfAce]

You're welcome. Glad it helped.
Please check the code you have against what's posted there now as I had made
a few changes to make it a bit more robust and I don't know if you got the
latest version or the original. :wink:

 

[-emma-]

Looks like i got the updated

 

[SidBord]

Looks like i got the updated

I always wondered ... does that hurt??