Return Pivot Data Grand Total In VBA

[Domski]

Hi,

I have a sheet with about twenty pivot tables whose page fields are controlled with VBA.

What I want to do is test each table to find out if the grand total is zero.

I thought this might return the total:

ActiveSheet.PivotTables("PivotTable1").PivotFields("M/Km").Value

in a similar way to the GETPIVOTDATA function works but all it returns is M/Km.

I guess I could use the GETPIVOTDATA function in VBA but thought there might be a better way.

Cheers,

Dom

 

[Andrew Poulsom]

Does this give you what you want?

Sub Test()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("M/Km").DataRange
        MsgBox .Cells(1, 1).Offset(.Rows.Count, 0).Value
    End With
End Sub
[code]

 

[Domski]

Hi Andrew,

Tried that and I get a Run-Time error '1004':

Unable to get the DatyaRange property of the PivotField class.

Dom

 

[Andrew Poulsom]

What is the actual name of the data field as it appears in the pivot table, eg "Sum of M/Km"?

 

[Domski]

Thanks, it worked if I changed the field name to that.

Much appreciated.

Dom

 

[DerekK]

Does anyone know how to return data within a Pivot Table, without referencing a specific range or cell in the VBA code?

The reason I ask is, the pivot table may change (i.e., additional rows, additional columns) and I'd like to be able to return the subtotals/grand total without having to update the VBA code.