PDA

View Full Version : Solved: Pivot table column totals



werafa
01-30-2013, 11:03 PM
Hi,

I am trying to read the column totals from a pivot into a variable.
There is a 2 tiered column structure:

Field1 Field2
item1:Item2 Item1:Item2

what I need is grandtotal = field1.item2.total etc.
I found instructions for getpivotdata, but need to set the 'item2' as pivotitems(2) as its name will change regularly, and I can't get the syntax right

suggestions greatly appreciated, and a solution will earn my undying gratitude.

Thanks
Tim
:igiveup:

Bob Phillips
01-31-2013, 02:47 AM
I am not sure I understand your issue, but you can just go to a cell, type - and then select the field in the pivot table and the GETPIVOTDATA function will be generated for you.

Other than that, can you give a sample workbook?

werafa
01-31-2013, 06:17 PM
The workbook will take a bit to prune, so I'll have one more go at explaining.

The pivot has consumption and cost fields as column headers, each with y1 and y2 data. how can I write the getpivotdata code so it will work even if an extra columns are added to the pivot table?
more specifically, how can i reference the 2nd subheading of the consumption field (y2) without defining it by name (which will change) or position in the table (Which could change).

the logic I am trying to validate is pivotfields("Consumption").columns(2).name

werafa
01-31-2013, 08:09 PM
Here is my code as it stands:
Function GetGrandTotals() As Variant
'read grand total consumption and cost from dataTable
'masterPivot and myUtility are global vars

Dim grandTotals() As Long
Dim myYear As String
Dim sheetName As String

sheetName = myUtility & "_Pivot"
myYear = masterPivot.ColumnRange.Value2(3, 2)
ReDim grandTotals(1 To 2)

grandTotals(1) = masterPivot.GetPivotData("Sum of Consumption (kWh)", Sheets(sheetName).Range("A3"), "Fin Year", "2012-2013").Value
'grandTotals(2) = ....

GetGrandTotals = grandTotals

End Function
I have also uploaded the pivot I am trying to draw the totals from (I hope)

werafa
01-31-2013, 08:35 PM
finally cracked it

Here is my solution.
don't put the :censored: pivot location inside the brackets as suggested by the non-vba version of the formula...... :banghead:

Function GetGrandTotals() As Variant
'read grand total consumption and cost from dataTable
'masterPivot and myUtility are global vars

Dim grandTotals() As Long
Dim myYear As String
'Dim ptLocation As String

'ptLocation = myUtility & "_Pivot!$A$3"
myYear = masterPivot.ColumnRange.Value2(3, 2)
ReDim grandTotals(1 To 2)

grandTotals(1) = masterPivot.GetPivotData("Sum of Consumption (kWh)", "Fin Year", myYear).Value
grandTotals(2) = masterPivot.GetPivotData("Sum of Total $", "Fin Year", myYear).Value

GetGrandTotals = grandTotals

End Function
for anyone else trying to understand the build, see the file above to nut out the columnrange reference - my field names will change, but their order won't. This just reads the supplied name from the 2nd column of the 3rd row in the column labels range.