If you have an Excel workbook with lots of tables and pivot tables, it can be hard to remember what they’re named, or what source data their using. To help you keep track of them, AlexJ is sharing the User Defined Function (UDF) that he uses in his files. With this code in your file, just add a formula, click on a cell, and show the Excel table name on the sheet.
The code is shown below, and there is also a link for downloading a sample file with the code installed in it.
Copy the Show Excel Table Name Code
To use this UDF in your file, copy the code shown below, and paste it into a regular code module in your workbook. There are instructions on my website, if you’re not sure how to do that.
Here is the code from AlexJ, to show an Excel table name on the sheet:
Function getObjName(rng As Range) As String Dim tbl As ListObject Dim pt As PivotTable Dim PivotName As String Dim TableName As String Dim qName As String Dim ptsName As String On Error GoTo Err_NoTable Set tbl = rng.Cells(1).ListObject TableName = "Table [" & tbl.Name & "]" On Error GoTo Err_NoQuery qName = "[" & _ tbl.QueryTable.WorkbookConnection.Name _ & "]" Res_Table: TableName = TableName & qName Res_Pivot: On Error GoTo Err_NoPivot Set pt = rng.Cells(1).PivotTable PivotName = "Pivot [" & _ rng.Cells(1).PivotTable.Name & "]" Res_PTS: On Error GoTo Err_NoPTSource ptsName = "[" & pt.SourceData & "]" Res_PTName: PivotName = PivotName & ptsName XIT: getObjName = TableName & PivotName Set tbl = Nothing Set pt = Nothing Exit Function Err_NoTable: 'Not a table check for Pivot TableName = "" Resume Res_Pivot 'Resume Err_NoQuery: 'No Query on the table qName = "" Resume Res_Table Err_NoPivot: 'Not a Pivot Table - exit PivotName = "" Resume XIT Err_NoPTSource: 'No Pivot source identified ptsName = "" Resume Res_PTName End Function
How to Use the Show Table Name Function
After you paste the Show Excel Table Name UDF code into your workbook, it’s ready to use. You can follow the steps below, to show the information about any table or pivot table in your file.
In this example, there are a couple of empty rows above the pivot table, so I added the formula there, in cell B1.
- Select the cell where you want to see the table name or pivot table name.
- Type an equal sign and the UDF name, followed by an opening bracket: =getObjName(
- Then, click on a cell in the table or pivot table, to refer to that cell
- To complete the formula, press Enter
Excel will automatically add the closing bracket, and the formula displays the table or pivot table information.
The Formula Results
In the screen shot below, the formula referred to a pivot table cell, and you can see the formula results in cell B1.
- NOTE: If the table name changes, the formula won’t update immediately. It will update when the workbook calculates.
There are three sections in the results, underlined and numbered in the screen shot.
- The type of object – Table or Pivot
- The object’s name
- The object’s source name, if available
In the next screen shot, the formula refers to a cell in a named table. The data is typed into the table, so there isn’t a source name available.
If the formula refers to a cell that isn’t in a named Excel table or a pivot table, the formula result will be an empty string.
Download the Sample File
To download the sample file, and test the Show Excel Table Name code, go to the AlexJ Sample Files page on my Contextures website. In the VBA section, look for VBA0003 – Show Table or Pivot Name on Sheet.
The zipped file is in xlsm format, and contains a macro – the Show Excel Table Name UDF code.