How can I get the ListObject object of a PivotTabl

Question

This question was originally posted as part of an answer by Hugh Seagraves on a related question. He "wanted to refer to a list object (a table) on one worksheet that a pivot table on a different worksheet refers to," and did have a fairly good answer, but asked if there was a more direct way. I tried to tack onto his answer, but others felt it was changing the purpose of his answer.

His reasoning was, "Since list objects are part of the Worksheets collection, you have to know the name of the worksheet that list object is on in order to refer to it." He provided some code, which would output the name of the table (ListObject) if you hadn't already created the Pivot Table. But I thought it might be helpful to have some generic code to get the ListObject object of any created Pivot Table, given the PivotTable name.

Answer 1

A PivotTable gets it's data from it's PivotCache. So you just need to use the PivotCache.SourceData property to query the ListObject name.

For instance, if I make up a PivotTable based on a ListObject, then if I select a cell in the PivotTable then I can use this:

? activecell.PivotTable.PivotCache.SourceData
Table1

Given that Table Names are unique in a workbook and are also Named Ranges, then to set a reference to the actual ListObject you just use something like this:

Set lo = Range(ActiveSheet.PivotTables("SomePivotTable").PivotCache.S‌​ourceData).ListObjec‌​t

Knowing this, we can write a function that accepts a PivotTable object and returns the ListObject that comprises the PivotTable's data source like so:

Public Function GetListObjectForPT(pt As PivotTable) As ListObject
On Error Resume Next ' In case the Pivot isn't created from a ListObject
Set GetListObjectForPT = Range(pt.PivotCache.SourceData).ListObject
End Function

...and you can use it like this:

Sub Macro1()
Dim pt As PivotTable
Dim lo As ListObject

Set pt = Worksheets("SomeWorksheetName").PivotTables("SomePivotTableName")
Set lo = GetListObjectForPT(pt)

End Sub