Rename sheet with content in specific cell


Don Doan

Hi there,
I don't know if this can be done using macro but if it's possible, please

Is there a way to rename the sheet with whatever the content in cell A1? And
do the same thing each sheet in the workbook.
For example, if cell A1 for the first sheet has the word "Project" then that
sheet should be renamed it to "Project". If cell A1 for the second sheet has
the word "Time", then that sheet should be renamed it to "Time". And keep on
doing that for all the sheets in the workbook regardless of how many sheets
there are.

Thank you in advance. :)


Sub RenameAllSheets()

Dim ws As Worksheet

For Each ws In Worksheets
On Error Resume Next
ws.Name = ws.Range("A1")
If Err.Number > 0 Then
On Error GoTo 0 'Reset error trapping ASAP
MsgBox "Could not rename sheet " & _
ws.Name & vbCrLf & vbCrLf & _
"Probably due to invalid sheet name"
End If
Next ws

End Sub

Gord Dibben

Sub Sheetname_cell()
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"
End If
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

Don Doan

Thank you all for your help. I guess you can do many things with VBA. THANK

Gord Dibben

Just about anything you need to do can be done with VBA.

Thanks for the feedback.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question