selecting a field from pivot table using vba macro

Question

I have created a pivot table using vba. I need help with the understanding of below-mentioned points.

1: I want to select(copy) values with certain filters (eg: Underlying price for Instrument Type = OPTCUR, Symbol = GBPUSD). Basically a VBA alternative for formula

GETPIVOTDATA("Underlying_price",$C$4,"Instrument Type","OPTCUR","Symbol","GBPUSD")

2: I want to set "show detail=True" without knowing cell details but the criteria as mentioned above.

3: when we set "show detail=True" a new sheet opens. i want to asign this sheet to a variable of type worksheet.

below is the SS of my pivot table. and TableName:="My_Pivot"

Please shows us what you tried, what problems you encounter, and what is the expected output (a table, just one line, etc). — EEM
@LOKE2707 Did you check my answer? Does it work or not? How about any kind of comment below it to give a direction? — Asger
Extremely sorry for the late reply I couldn't reply sooner. @ASger your solution worked for all my questions. myCell.ShowDetail was a little tricky, thank you for your example that really helped. — LOKE2707

Asger Asger · Accepted Answer · 2019-04-24T11:45:37

You get the relevant cell with PivotTable.GetPivotData
The new worksheet with details of this cell is shown by Range.ShowDetail = True.
Directly after that, the new ActiveSheet is the wanted one.

Here is a function to get the wanted worksheet with the details for a specified data field:

Private Function GetDetailSheet(pt As PivotTable, Val1 As String, Val2 As String) As Worksheet
    Dim myCell As Range
    With pt
        Set myCell = .GetPivotData(.DataFields(1).Name, _
            .RowFields(1).Name, Val1, _
            .RowFields(2).Name, Val2)
    End With
    myCell.ShowDetail = True
    Set GetDetailSheet = ActiveSheet
End Function

It can be used like this:

Private Sub Test()
    Dim ws as Worksheet
    Set ws = GetDetailSheet(ActiveSheet.PivotTables("My_Pivot"), "OPTCUR", "GBPUSD")
    ws.Name = "Details OPTCUR GBPUSD"
End Sub

selecting a field from pivot table using vba macro

2 Answers