1004 Error. That name is taken

[qamex]

Hi team,
Code below. I
t is meant to run through the work book and format the Tab name with the name in D6. It has been working fine for a couple months but now it refuses to budge from this error on this line.
There are on 10 names that appear repeatedly.

Sub Tab_name()
Dim ws As Worksheet, wsName$, wsCount%, dict   ' $ is short for " As String" and % for " As Integer"
    Set dict = CreateObject("Scripting.Dictionary")
    For Each ws In Sheets
        wsName = ws.Range("D6").Value
        wsCount = IIf(dict.Exists(wsName), dict(wsName) + 1, 1)
        dict(wsName) = wsCount
        If wsCount = 1 Then
            ws.Name = wsName
        Else
            If wsCount = 2 Then Sheets(wsName).Name = wsName & 1 ' optional to rename the previous Ohio sheet to Ohio1
            ws.Name = wsName & wsCount
        End If
    Next ws
End Sub

 

[Peter_SSs]

You will get that error if a sheet to the right of the one the code is working on already has the name of the D6 value in your current sheet. For example if the left hand sheet has "Ohio" in D6 but the second sheet from the left is called "Ohio" then you will be trying to rename the first sheet the same as the second - not allowed.
You need to go right through all the sheets first and put their names in to the dictionary and then work through again dealing with what to do about re-naming.

 

[qamex]

Thanks mate. That advice helped get it working. If only you could eliminate the human element that creates most of the error.

 

[Peter_SSs]

Thanks mate. That advice helped get it working.

You're welcome. Glad to help.