Question about tab naming and referenced cells

[Guest]

Is there a way to have a certain worksheet assume the text in a cell (i.e.
sheet 2 renaming itself to whatever is in cell a4 of sheet 1, as well as
sheet 3 renamed to what is in a5 of sheet 1, and so forth), and then
automatically updating any reference to the renamed sheet throuhout the
workbook.

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click your worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
If Not Intersect(Target, Range("A4").Resize( _
Worksheets.Count, 1)) Is Nothing Then
On Error Resume Next 'in case invalid sheet name
For i = 2 To Worksheets.Count
Sheets(i).Name = Cells(i + 2, 1).Value
Next i
On Error GoTo 0
End If
End Sub

The update to references will occur automatically.

 

[Guest]

Thank you. I'm not very good at creating VB code, but I am good enough to
know how to modify it should I need to. Either way this does exactly what I
need it to do.

Thanks again,
mbowen83

 

[Guest]

I have the same question but just want to link one worksheet tab name
automatically to a cell in the worksheet. Change the cell and the tab name

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html