View Full Version : Autimatize worksheet names

Sumit Jain
07-18-2013, 11:01 PM
hi all

I wanted some help in automatizing my worksheet names.

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.

07-19-2013, 03:19 AM
Sub renameSheets()
For Each sh In Sheets
newname = sh.range("B1").text
sh.Name = newname
End Sub

07-19-2013, 09:12 AM
Hi, Sumit Jain,

the whole contents of B1 or just the red one?

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 Ciao,

Sumit Jain
07-25-2013, 04:35 AM
Thanx Patel and Hahobe for reply