When you create a table in Excel (I'm using Excel 2007) you can give it a name that shows up in the Name Manager dialog. However, the name has workbook scope. I'd like to have a table with a name scoped only to the worksheet if possible (just like named formulas/ranges can be). Is it possible?

For example, I'd like to have a table named 'Notes' on several different worksheets. Any given table would only be referenced from the worksheet it is actually on, and I'd rather not have to have each worksheet have a table with a different name. I'd also rather be able to see the table name grouped with other named formulas scoped to that worksheet when I sort by scope in the Name Manager dialog.

  • Have you tried testing in VBA using the same syntax as sheet-specific named ranges? I don't have 2007 here or I'd check it for you. Commented Aug 9, 2010 at 17:09
  • @Lance, if I try to name a ListObject from VBA to e.g. "Scratch!MyTable", the call appears to work, but the name gets silently changes to "Scratch_MyTable" and still has workbook scope. If I just try to set it to "MyTable" and there is already a table named that on a different sheet, it ends up "MyTable_1". Of course, trying those things from the table design menu results in "can't do this" type messages.
    – jtolle
    Commented Aug 9, 2010 at 22:45
  • It's interesting that the ListObjects collection is local to the Worksheet object, though. It seems like Excel would be fine with two tables in two different collections having the same name, but I'm starting to think the answer to my question is "no"...
    – jtolle
    Commented Aug 9, 2010 at 22:47

3 Answers 3


For completeness, I'm pretty sure the answer to my question is "no".

Of course I'll un-accept this and accept an answer that shows that this is wrong, or one that explains why it's right.

  • 1
    This is a truly unfortunate design decision on the part of the Excel team. Globals are evil, and Workbook-scoped names are darn close to global scoping. Considering that Table Names show up in the Name Manager, one really wonders about the reasoning behind this decision.
    – user36800
    Commented Apr 9, 2018 at 18:55

This may be helpful as a workaround:

    'Given a worksheet name and a table(ListObject)name "prefix," return the actual name of the ListObject on the sheet.
    'For example, if a worksheet containing a table called "ConfigTbl" is duplicated, it will get a name
    'like "ConfigTbl3279" on the worksheet copy.
    'This is a workaround for the fact that Tables are worksheet-level objects;
    'Excel doesn't have local/worksheet-level tables.
    'If you pass in one of the "Special Item" qualifiers, then the function will return the specified
    'range as an object (e.g., "[#All]" for the whole table or "[#Headers]" for the column names).
    'Returns #N/A if error.       

     Public Function tableName(wksht As String, tblName As String, Optional specialItem)

     Dim lObj As ListObject

     On Error GoTo ErrHandler
     tblName = UCase(tblName)

     For Each lObj In Worksheets(wksht).ListObjects
         If (UCase(Left(lObj.Name, Len(tblName))) = tblName) Then
             tableName = lObj.Name
             If Not (IsMissing(specialItem)) Then Set tableName = Evaluate(tableName & specialItem)
             Exit Function
         End If
     Next lObj

        tableName = CVErr(xlErrNA)

    End Function

I've been running into the same problem. In my VBA code, I resorted to looking at the objects for the active worksheet and checking against the first 6 letters (when I copy a worksheet with tables, Excel 2007 simply appends a number to the end of the table names).

' First get table names on this worksheet (copying worksheet changes names of tables)
Dim oSh As Worksheet
Dim oLo As ListObject
Set oSh = ActiveSheet
For Each oLo In oSh.ListObjects
    Application.Goto oLo.Range
    'MsgBox ("Table found: " & oLo.Name & ", " & oLo.Range.Address)
'    pathsTable =
    Select Case Left(oLo.Name, 6)
        Case "tblDec"
            decTable = oLo.Name
        Case "tblPat"
            pathTable = oLo.Name
        Case "tblPro"
            processTable = oLo.Name
    End Select

Then in the rest of my code, I use the new names: decTable, pathTable, and processTable.

A simple work-around is to create names with local scope that refer to the table. So you'll have two names for each table - one with global scope and one with just the current worksheet scope. (In the Define Name dialog box, name the new range "Notes", change scope to the worksheet and the "Refers to" to =Table1 (or whatever Excel named the table)). Do this on the first worksheet, then copy the worksheet. The tables will have the same names with scope for just that worksheet. The drawback is that you can't use normal table referencing when using the duplicate name, e.g. "=average(Names[Column1])" doesn't work. :( If you are using just a small selection of columns of the table, you could create a name for each column that you want to use elsewhere, "myColumn1" refers to "=Table1[Column1]".

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .