Naming tables - name should be unique

[Yamezz]

Something went awry in my workbook and I'm having to rebuild a drop-down data validation list from the 'Supplier' column of a table. I've had to start again and re-input the data for the table and rename it. I've deleted all the references to this in the Name Manager to start again. However, when I try to name the new table with the same name it used to have I get an error: "This name already exists. Names should be unique" When I look in the Name manager, there is no such name already listed.

To be clear, I want to rebuild the following:
1) Cell G7 on the 'Journal Entry' sheet containes a data-validated drop-down list.
2) The source for this is =DataRecurringJournal
3) In the Name Manager I have DataRecurringJournal=TableRecurringJournal
4) On the 'Data' sheet I have a table called TableRecurringJournal
5) This just lists the Supplier column of another table via =TableRecurringJournalDetails[@Supplier]

Point 1, 2, 3 and 5 are working fine, but when I try to rename my new table to TableRecurringJournal to achieve point 4) the error message pops up.

I'd be grateful for any ideas.

 

[mmhill]

It's probably something simple like the existing table is on a hidden worksheet. If that is the case, the table name will not show up in the Name Manager. If it is VeryHidden, you need the sheet name or codename to unhide it.

Try running the macro below. It will look for a specific table name in the active work. If found, it will print in the debug window: the Table Name, the name of the worksheet it's on, the codename of the worksheet it's on, the worksheet's visibility status, and the range address of the table. With that info, you should be able to fix your problem.

Sub FindTable()
Dim strSearchName$, ws As Worksheet, oList As ListObject, booFound As Boolean

strSearchName = "\\Table" '<<< Enter your table name here

Debug.Print Chr(13) & "--- FOUND TABLES ---"
For Each ws In ActiveWorkbook.Worksheets
    For Each oList In ws.ListObjects
        If StrComp(oList.Name, strSearchName, vbTextCompare) = 0 Then
            Debug.Print oList.Name & String(2, vbTab) & _
                        oList.Parent.Name & String(2, vbTab) & _
                        oList.Parent.CodeName & String(1, vbTab) & _
                        Choose(oList.Parent.Visible + 2, "Visible", "Hidden", "N/A", "VeryHidden") & String(1, vbTab) & _
                        oList.Range.Address
                        booFound = True
        End If
        If booFound Then Exit For
    Next oList
    If booFound Then Exit For
Next ws
End Sub

 

[Yamezz]

Thanks for your code.

I ran it, but it didn't find anything... however I just tried to rename the table again and this time it worked. Maybe your code told Excel what I've been trying to tell it for hours! HaHa.