Rename worksheet

[Yuriy]

Does anybody know a way of renaming all worksheet in a
workbook after a certain cell in each worksheet; that is,
each sheet's name comes from, say, A1 of that sheet.

Thank you, that would be so much helpful.
Yuriy

 

[Paul B]

Yuriy, try this macro

Sub RenameSheets()
'Renames each sheet to the value of A1 on that sheet
On Error Resume Next 'In case A1 contains an invalid name
For Each sht In ActiveWorkbook.Worksheets
sht.Name = sht.Range("A1").Value
Next
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Gord Dibben]

Yuriy

If the data in each A1 is unique, no problem with code.

This macro will change each sheet name to whatever is in A1.

If duplicates, you will be asked to manually enter a name.

Sub Sheetname_cell()
'Ron de Bruin
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Range("A1").Value
'next lines cover duplicate names
If Err.Number > 0 Then
MsgBox "Change the name of : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP

 

[Yuriy]

Thank you very much, Gord and Paul, it works!
Really amazing for a person not quite familiar with Visual
Basic.

One comment, though, which I hope may be interesting or
useful for you. In my worksheet, A1 cells on each
worksheet are linked to a summary statement which is the
last worksheet in the workbook. When I run Gord's macro,
it tries to rename this last worksheet and then asks to do
it manually beacause in my arrangement its A1 is the
source for the first worsheet's A1 (A2 - source for the
second sheet's A1 and so forth). When I run Paul's macro,
it doesn't touch the source worksheet and doesn't prompt
anything.

Thank you again, you saved me a couple of hours of tedious
work!