Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Grand Total (2003)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Grand Total (2003)

    You can use a formula to get the grand total of a field in a pivot table:
    <code>
    =GETPIVOTDATA("FieldName",Sheet1!$A$4)
    </code>
    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:
    <code>
    Range("B1").Formula = "=GETPIVOTDATA(""FieldName"",Sheet1!$A$4)"
    </code>
    Note the use of double double quotes within the formula string to indicate that the formula contains quotes.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Grand Total (2003)

    Thanks Hans that worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •