Extracting Data from Pivot Table using VBA?

T

trickytom

New member
Joined
Mar 18, 2016
Messages
2
Reaction score
0
Points
0
I'm having a little trouble understanding how to go about extracting Pivot Table reports using VBA.

I know that I have to declare my existing pivot table. That's easy.

I want to extract data from it though.

If my Pivot table looks like the below: How do I extract the actual values contributing to the 'count of test_list' = 4 highlighted in red.
When I click on the number '4' below it gives me a separate worksheet with all the values of the letter 'a' contributing to the count of 4. Is it possible to extract this data using VBA? I want to do this so I can print it in a variety of formats automatically. Thanks

Row Labels Count
of test_list
a4
b2
c2
d2
e1
f1
g1
I1
j1
k1
m1
Grand Total17


Code: 
Sub pivot_table_test()

Dim pvt As PivotTable
Set pvt = Sheets("Sheet2").PivotTables(1)  'done

'now I want to extract data from it. 

End Sub
 
Last edited:
Just to add. I'm trying the bellow and I just get the error "Application defined or Object Defined error".

I just can't see what I'm doing wrong?

Sub getdata()


Code: 
Dim pvt As PivotTable
Dim rng As Range

Set pvt = Sheets("Sheet2").PivotTables(1)

Set rng = pvt.GetPivotData("Count of test_list", "a", "4")  'error occurs here?


End Sub
 
You must log in or register to reply here.
Back
Top