Rename tab based on cell value - Macro

[redjay]

Hi all

I want some VBA code that will cycle through all the worksheets in a workbook and rename each tab/sheet based on the value of a cell in that sheet (B17).

The cell is always B17 but on some of the sheets, B17 is blank, in which case it is fine to not rename.

I have tried the below code but get a 'Name of object'_worksheet' failed' error message.
Any ideas anybody? Thanks

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("B17").Value
Next
End Sub

 

[Bill Morris]

Thanks for your help guys. Worked a treat!