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
help.

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.

 

[OssieMac]

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"
Err.Clear
End If
On Error GoTo 0
Next
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
YOU!!!!

 

[Gord Dibben]

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

Thanks for the feedback.


Gord