0

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.

GlennFromIowa
  • 1,616
  • 1
  • 14
  • 19

1 Answers1

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
jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
  • Actually, this gives the List Object/table name, but does *not* expose the parent Worksheet's name, nor give you any way to reference the List Object. To reference the List Object, you'll still need the Worksheet name. The objective was to have a replacement for `Set LO = ActiveSheet.ListObjects("YourListObjectName")`, when the List Object is on a sheet other than the ActiveSheet. – GlennFromIowa Oct 17 '17 at 20:15
  • 1
    Yes it does, although you're correct that my answer doesn't show the method. Given Table1 is a unique named range, then you can simply use this: `Set lo = Range(ActiveSheet.PivotTables("SomePivotTable").PivotCache.SourceData).ListObject` – jeffreyweir Oct 17 '17 at 20:42
  • Yup, that does it! Thanks for editing your answer. And assuming the Pivot is created from a ListObject the sheet name can be gotten with this one-liner: `? Range(Worksheets("SomeWorksheetName").PivotTables("SomePivotTableName").PivotCache.SourceData).ListObject.Parent.Name` – GlennFromIowa Oct 18 '17 at 17:10