Autimatize worksheet names

[Sumit Jain]

hi all

I wanted some help in automatizing my worksheet names.

Description:
I have a workbook with multiple sheets. The worksheet names should be coming from the cell B1 of the same worksheet:

Moreover, Cell B1 in each sheet should contain names as follows:

A1.01 - Bombay
A1.02 - Delhi
A1.03 - Kolkata
A1.04 - Chennai

where text in red color are variable and will change from case to case.

I am attaching a sample file.

[patel]

[VBA]Sub renameSheets()
For Each sh In Sheets
newname = sh.range("B1").text
sh.Name = newname
Next
End Sub[/VBA]

[HaHoBe]

Hi, Sumit Jain,

the whole contents of B1 or just the red one?

[vba]Sub VBA_Ex46883()
Dim ws As Worksheet

On Error Resume Next

For Each ws In Sheets
ws.Name = ws.Range("B1").Value
' ws.Name = Trim(Mid(ws.Range("B1").Value, InStr(1, ws.Range("B1").Value, "-") + 1))

If Err <> 0 Then
MsgBox "Could not rename sheet '" & ws.Name & "'. Please check", vbInformation, "Check contents of cell B1"
Exit Sub
End If
Next ws

End Sub[/vba] Ciao,
Holger

[Sumit Jain]

Thanx Patel and Hahobe for reply