Tab Names

[Guest]

Is there a way to have the worksheet tab names reflect the same name that is
in another worksheet and another cell without having to change (18) worksheet
tab names. The cell and worksheet would remain the same, however occasional
the name in that cell would change (as per new release's, P1, P2, P3, etc)
and I would like the tab to automatically update the new name.

Any thoughts

Thanks
Frank

 

[Bob Phillips]

Something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Worksheets(.Row).Name = .Value
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob:

Don't mean to sound dumb, but how do you run this after you put it in the
worksheet event code. When I try to run a macro it does not list this. I
can see it but when I hit the run button it goes to another macro. This is
my first time putting a macro in one of the worksheet event codes.

Thanks
Frank

 

[Bob Phillips]

Change one of the cells in H1:H10 of the worksheet that you attached the
code to, and note the results. The code is triggered by the change.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)