Referencing Pivot Table Grand Totals

[JimRoy]

I have a dynamic pivot table that will shrink and grow depending on the data. On average there are 1000 rows of data. I wish to display the Grand Totals at the top of the report.

I am unable to use the trick of creating an additional field within the table with the same data for all rows and then using it to display sub totals at the top because I have other data that I don't want displayed using the "Show Sub Totals" option.

Is there any way to use the GetPivotData reference to the Grand Totals when the fields constantly move? Is there a way I could build a macro to find the Grand Totals Columns and then copy and past them to the top when the report is refreshed? I have had no success yet.

Any assistance would be appreciated.

 

[fhqwgads]

in the cell you want to total press = and then click on the cell with the total in the pivot table. if this gives you a cell reference like A1 instead GETPIVOTDATA(), with your pivot table selected, go to the options tab, and on the left under PivotTable Name there should be another options tab. click the little arrow and make sure Generate GetPivotData is checked and repeat the first step.

hope that works, i'm not sure if it's different in later versions of excel. i'm using 2007 myself

 

[JimRoy]

That worked! Life saver!

Thanks