Rename sheet with content in specific cell

D

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. :)
 
O

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
 
G

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
 
D

Don Doan

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

Gord Dibben

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

Thanks for the feedback.


Gord
 

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

Top