Renaming specific excel sheets using macro and cell value

[jr0124]

Hello,

I am looking for some help, I have a spreadsheet that has 8 tabs, 5 of those I was looking to rename based on cell B14 in that sheet. I was trying something like this but I can't figure out how to have it select the correct B14 value and change only those tabs I need to rename. The current sheets are numbers, 1, 2, 3, 4 and 5.

Any ideas on how to adjust to accomplish what I am looking to do?

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("B14")) >0 Then
ws.Name = ws.Range("B14").Value
End If
On Error Goto 0
If ws.Name <> ws.Range("B14").Value Then
Msgbox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

 

[Fluff]

If by

The current sheets are numbers, 1, 2, 3, 4 and 5.

You mean the sheet index, try

Sub tabname()
    Dim Cnt As Long
    
    For Cnt = 1 To 5
        With Sheets(Cnt)
            If Len(.Range("B14")) > 0 Then
                On Error Resume Next
                .Name = .Range("B14").Value
                On Error GoTo 0
            End If
            If .Name <> .Range("B14").Value Then MsgBox .Name & " Was Not renamed, the suggested name was invalid"
        End With
    Next Cnt
End Sub

 

[jr0124]

You are amazing, thank you!

 

[Fluff]

Glad to help & thanks for the feedback