get Pivot Value (Grand Total ) using vba?

1
votes

I have a Pivot Table and I want a value from that Pivot Table which is the Grand Total value. If you see the below picture, I want the value "88%". This value is in cell C24 . And it is in the Compliance Field (Column C) and the row is the Grand Total. Above the Pivot Table I already have the filters applied for filtering my data. I just want the vba code for this cell/field value. This is what I tried.

Dim rtot as Double
rtot = Worksheets("ABC").PivotTables("PivotTable2").PivotValueCell(3, 24).Value

enter image description here

2
excelvba
You will do better to reference the DataBodyRange of the pivot table, as detailed in this answer - PeterT
Take a look here for how to reference regions in a pivot table - peltiertech.com/referencing-pivot-table-ranges-in-vba - Tim Williams
Thanks @PeterT ......I modified the code according to the link you shared and it worked. - Roy
Thank you @TimWilliams. This is very useful. - Roy

2 Answers

2
votes

I changed my code using the link in the comments and it worked. So just sharing it so that its useful for other people.

Set pt = Worksheets("ABC").PivotTables("PivotTable2")
            rtot = pt.DataBodyRange.Cells(pt.DataBodyRange.Cells.Count).Value
0
votes

You could use Evaluate on the Getpivotdata formula:

rtot = Application.Evaluate("=GETPIVOTDATA(""COMPLIANCE %"",ABC!$A$13)")

Change cell address and sheet to yours (topleftcell of pivot)