How to display table or pivot table name in a cell in Excel?

This article is talking about displaying a specified table or pivot table name in a cell in Excel.

Display table or pivot table name in a cell with VBA code

Display table or pivot table name in a cell with VBA code

The following VBA code can help you display a specified table or pivot table name in a cell. Please do as follows.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the Module window.

VBA code: Display table or pivot table name in a cell

Function getObjName(rng As Range) As String
‘Updated by Extendoffice 20180420
    Dim xTable As ListObject
    Dim xPivotTable As PivotTable
    Dim xTableName As String
    Dim xPtName As String
    On Error Resume Next
    xTableName = ""
    xPtName = ""
    Set xTable = rng.Cells(1).ListObject
    xTableName = "Table[" & xTable.Name & "]"
    If xTable Is Nothing Then
        Set xPivotTable = rng.Cells(1).PivotTable
        xPtName = "Pivot [" & xPivotTable.Name & "]"
    End If
    getObjName = xTableName & xPtName
End Function

3. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.

4. Select a blank cell, enter formula =getObjName(A2) into the Formula Bar and then press the Enter key. See screenshot:

Notes:

Related articles:

• How to list all table names in Excel?
• How to keep table expandable by inserting table row in a protected worksheet in Excel?
• How to reorder position of columns in table in Excel?
• How to hide zero value rows in pivot table?