Automatic Sheet Naming

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Lowenstein - 11 Mar 2008 13:26 GMT

If I have a list of people in the A column, is there a way for the worksheet
tabs reflect those names automatically?

Example:

Sheet1:
A1: Joe
A2: Bob
A3: Bill

Sheet2 renames to what is in A1, or Joe
Sheet3 renames to what is in A2, or Bob
Sheet4 renames to what is in A3, or Bill

Then I rename A1 to Albert, and Sheet2 or the “Joe” sheet automatically
becomes Albert now. Thanks!

Reply to this Message

Gary''s Student - 11 Mar 2008 13:42 GMT

Sub renamre()
Sheets("Sheet1").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
Sheets(i + 1).Name = Cells(i, 1).Value
Next
End Sub

Signature

Gary''s Student - gsnu200772

Reply to this Message

Jeff Lowenstein - 11 Mar 2008 14:18 GMT

> Sub renamre()
> Sheets("Sheet1").Activate
[quoted text clipped - 3 lines]
> Next
> End Sub

Gary,

Thank you for the answer, but will this be automatic - i.e. as soon as
cell A1 is changed, sheet2 is renamed?

Thanks

Reply to this Message

Gary''s Student - 11 Mar 2008 14:33 GMT

Put this Event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
n = t.Row
Sheets(n + 1).Name = t.Value
End Sub

Signature

Gary''s Student - gsnu200772

> > Sub renamre()
> > Sheets("Sheet1").Activate
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

LRay67 - 11 Mar 2008 14:49 GMT

Gary, how would you rename the tab with a Text Box on a worksheet? Not a
userform

I've tried the following doesn't seem to work.

Private Sub TextBox127_LostFocus()
ActiveSheet.Name = Me.Textbox127.value
End Sub

> Put this Event macro in the worksheet code area:
>
[quoted text clipped - 20 lines]
> >
> > Thanks

Reply to this Message