CODEWITHSUNDEEP
excelvba
Roy
Roy

Reputation: 85

get Pivot Value (Grand Total ) using vba?

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

Upvotes: 1

Views: 2532

Answers (2)

Roy
Roy

Reputation: 85

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

Upvotes: 4

EvR
EvR

Reputation: 3498

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)

Upvotes: 0

Related Questions