Reference Pivot Tables by name 1

[mintjulep]

Excel provides the GETPIVOTDATA() function.

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

Microsoft defines the pivot_table parameter this way: "A reference to any cell, range of cells, or named range of cells in a PivotTable. This information is used to determine which PivotTable contains the data that you want to retrieve."

So, in use on a sheet it looks like this: =GETPIVOTDATA("Row Cost",'Break Down'!$A$3,"Group",[@Group])

Pivot Tables have names, and I want to be able to use that name on the sheet. So far I have:

Public Function GetPivotTopLeft(mySheet As String, TargetPT As String) As String

Dim PT As PivotTable
Dim TopLeftCell As String
Set PT = Worksheets(mySheet).PivotTables(TargetPT)
TopLeftCell = Split(PT.TableRange1.Address, ":")(0)
GetPivotTopLeft = "'" & Sheets(mySheet).Name & "'" & "!" & TopLeftCell
End Function

This works, but only by using it inside INDIRECT(), like this:
=GETPIVOTDATA("Hours Allocated",INDIRECT(GetPivotTopLeft("Break Down", "TechBreakDown")))

How can I get away from the need to use INDIRECT()?

=GETPIVOTDATA("Hours Allocated",GetPivotTopLeft("Break Down", "TechBreakDown"))

What VBA variable type does my GetPivotTopLeft() function need to return to make the native GETPIVOTDATA() function happy for the pivot_table parameter?

How do I build that type in my VBA function?

 

[combo]

The second argument in GETPIVOTDATA is range, so:

Public Function GetPivotTopLeft(mySheet As String, TargetPT As String) As Range
Dim PT As PivotTable
Set PT = Worksheets(mySheet).PivotTables(TargetPT)
Set GetPivotTopLeft = PT.TableRange1.Cells(1)
End Function

In my tests [tt].Cells(1)[/tt] was not necessary, however it returns the first cell in range.

Pivot table has GetPivotData property, you can extend your function to return final data.


combo

 

[mintjulep]

@combo

Thanks, that works.

I had tried something similar earlier, but it missed the need to Set the final value because it's an object.

Set GetPivotTopLeft = PT.TableRange1.Cells(1)