Pivot Table Grand Total (2003)

Topic

New Reply

Hello,
I have a pivot table that the grandtotal changes and with it where the grandtotal appears. For example, it can move from row25 to row 26 depending on the data. I am in the process of writing a VB code to add a sheet and include the grandtotal no matter where it appears. Is there a way to do this? For example when I write the VB code it would be as follows

Sheets.add.name = “test”
Range (“B1”).select = “This is where the command would appear for the pivot table grand total.”

Any help would be great.

Reply | Quote

Replies

You can use a formula to get the grand total of a field in a pivot table:

=GETPIVOTDATA("FieldName",Sheet1!$A$4)

FieldName is the name of the field whose grand total you need, and Sheet1!A4 is an arbitrary cell in the pivot table. To do this in code, you can put a formula in the target cell:

Range("B1").Formula = "=GETPIVOTDATA(""FieldName"",Sheet1!$A$4)"

Note the use of double double quotes within the formula string to indicate that the formula contains quotes.

Reply | Quote

Thanks Hans that worked.

Reply | Quote