Use VBA to put pivot table subtotal into a variable

Hi All

Does anyone know if it's possible to extract a pivot table subtotal with VBA?

I'd like to get the value 13 from the below table into a variable
i.e where 'RESULTS' field = 'TBC'

And it is possible, how would I handle errors if there are no TBC results in the pivot table?

Sub GetTotalTBCs()

Dim TBCs As Integer

TBCs = ActiveSheet.PivotTables("PivotTable1").PivotFields("Result") = "TBC".Subtotal.value

End Sub

Pivot Table - Bobs Jungle Gym.png

Any advice appreciated
All the best
Vicky

You can use Getpivotdata for that:

ActiveSheet.PivotTables(1).GetPivotData("Case Number", "SLA3->Appointment to Advice Sent (HR) (Result)", "TBC")

for example. Put an On Error Resume Next before it, and test the err.number if you want to know if the field isn't there.

Glad to help.