Renaming tabs "400" macro run error

[Aeafa]

Hello!

I am having trouble running a macro that was written to re-name the tabs in a worksheet.
It comes up with a box and "400".

Now my main source of confusion is that I have the exact same code & references in another spreadsheet and it works 100% fine.
Does anyone know what could be causing this to stop working in this particular spreadsheet?

Many many thanks in advance.

Oh, and this is the code I am using.

Sub RenameTabs()
For x = 1 To Sheets.Count
If Worksheets(x).Range("A4").Value <> "" Then
Sheets(x).Name = Worksheets(x).Range("A4").Value
End If
Next
End Sub

 

[My Aswer Is This]

Maybe one of the values in the Range("A4").
Are duplicates or improper values for a sheet name

 

[Joe4]

If you adding some error handling, you can get it to tell you which sheet/value is giving your problems, i.e.

Sub RenameTabs()

    Dim x As Long
    Dim myName

    On Error GoTo err_chk
    For x = 1 To Sheets.Count
        myName = Worksheets(x).Range("A4").Value
        If myName <> "" Then
            Sheets(x).Name = myName
        End If
    Next
    On Error GoTo 0
    
    Exit Sub
    

err_chk:
    If Err.Number = 400 Then
        MsgBox "Error on sheet " & x & " when name is " & myName
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[Aeafa]

Thank you both so so so much! I didn't even think of that! -facepalm-

Turns out I was trying to rename a tab to the same name that one of them already was.
I can't believe I missed that... -shakes head in disgrace-

Again, thank you so very much, ya'll really have no idea how much I appreciate this!!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you both so so so much! I didn't even think of that! -facepalm-

Turns out I was trying to rename a tab to the same name that one of them already was.
I can't believe I missed that... -shakes head in disgrace-

Again, thank you so very much, ya'll really have no idea how much I appreciate this!!