VBA code to select Grand Total Row values in Pivot Table

[T_Bos]

Hello Community,

I need some help to come up with a VBA code to select just the values of the Grand Total row and paste it transpose in other cells. For example, for the PT below, I want to grab the values of the Grand total and paste it transposed in cells in another sheet. Thanks for any help in advance.

 

[mart37]

When you have the titles of the pivotcolumns on sheet 2 then maybe you can use:

    Dim s As Range
    Dim p As Range
    Set s = Worksheets("sheets2").Range("A2:A5") 'range with titles
    Set p = Worksheets("sheets1").Range("A1") 'first cell in pivottable
    For Each c In s
        c.Offset(0, 1).Value = Application.Evaluate("=GETPIVOTDATA(""" & c.Value & """," & p.Address & ")")
    Next

 

[T_Bos]

When you have the titles of the pivotcolumns on sheet 2 then maybe you can use:

    Dim s As Range
    Dim p As Range
    Set s = Worksheets("sheets2").Range("A2:A5") 'range with titles
    Set p = Worksheets("sheets1").Range("A1") 'first cell in pivottable
    For Each c In s
        c.Offset(0, 1).Value = Application.Evaluate("=GETPIVOTDATA(""" & c.Value & """," & p.Address & ")")
    Next

Hello Mart37,

Thank you for your help. I am trying to understand your suggested code. How do I paste the values in Sheet 2? How does the code grab the column totals for Absent, Early, On-Time, and Late?

 

[mart37]

A2 is a cell with a columntitle: Absent
c is a cell from the range A2:A5
c.Offset(0, 1) is the cell on the right of the titlecell: B2
In this cell B2 paste the code the value: 478 with a normal excelfunction: GETPIVOTTDATA
Application.Evaluate("=GETPIVOTDATA(""" & c.Value & """," & p.Address & ")"): with EVALUATE can you calculate a normal excelformula. In this case I want to calculate with the function: GETPIVOTDATA.
The function GETPIVOTDATA can get information from a pivottable. So you can get values from items or in this case from a totalcell.
GETPIVOTDATA needs a columntitle --> c.value has the title of the first column: ABSENT
GETPIVOTTABLE needs to know the location of the pivot table--> p.address is the address from the first cell in the pivot table