Auto renaming of Worksheets?

[Chris Mitchell]

What I want to do is set up a Workbook with Sheets N01 - N30, and have an
index Sheet such that when N01 in the index is overtyped with 'Fred'
Worksheet N01 is renamend 'Fred'. This way I can set up formulae etc which
will auto update as and when Sheet names are updated. Is this possible?

An earlier reply by Bondi (Thanks Bondi) pointed to
http://www.ozgrid.com/Excel/variable-worksheet-names.htm, but either I'm not
reading this right or it's not quite what I need.

TIA.

Chris.

 

[Don Guillett]

If you used the macro I provided to get an index and you had a title in row
1 then

Sub listsheets()
For i = 1 To Worksheets.Count
Cells(i + 1, "a") = Sheets(i).Name
Next i
End Sub

right click sheet tab>view code>insert this. A change to the listed name
will now chg the sheet name.
I would run my original macro before in case you have moved sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Sheets(Target.Row - 1).Name = Target
End Sub

 

[Chris Mitchell]

Thanks Don.

Our most recent posting must have passed each other in the ether.

 

[Don Guillett]

You really should stay in the ORIGINAL thread. Send me (address below) your
workbook if you like.

 

[Chris Mitchell]

Thanks for the offer Don, I've sent the file to you.

 

[Don Guillett]

File returned to Chris with desires

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 Or Target.Column <> 2 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Sheets(Target.Row).Name = Target
Application.EnableEvents = True
End Sub