View Full Version : [SOLVED] Pivot Table Data

03-23-2016, 05:53 AM
Hello folks,

I could create a pivot table if required. but you may be able to answer my query without one.

I want to be able to double click the Grand total cell on a pivot table to open a new sheet with the data.

Problem is as the Pivot Table Grand Total cell may move to a different cell in a column each time the data is changed.
How to I make that Grand total Cell reference dynamic so that it locates the new cell if the data changes.

03-23-2016, 06:54 AM
Assuming a pivottable object variable pt:

With pt.TableRange2
.Cells(.Cells.Count).ShowDetail = True
End With

03-23-2016, 08:16 AM
If there's more than one grand total at the bottom of the Values/Data area of the pivot table (but will it make any difference to the new sheet?), this will get the rightmost (or only) one:
pt.GetPivotData(pt.DataFields(pt.DataFields.Count).Name).ShowDetail = True
However, if there is nothing in the Values/Data area, Aflatoon's method will still work whereas mine won't!

03-23-2016, 10:41 AM

Thanks being thick here how do I set the 'pivottable object variable pt:' Also if there were more than one Pivot Table on the worksheet would it still work, the Pivot Table I'm working on is called PivtTable6.

03-23-2016, 01:19 PM
Dim pt as Pivottable
set pt = activesheet.pivottables("PivtTable6")
for example.

03-23-2016, 01:33 PM
Marvellous Aflatoon, that did do the trick. many thanks indeed. Full of admiration for you fellas I seem to get 90% there and then cannot work it out.

03-23-2016, 02:45 PM
p45cal That's great it works a treat. How does it work though and suppose I wanted to take say the leftmost total?

03-23-2016, 02:56 PM
leftmost total of the values/Data area of the pivot:
pt.GetPivotData(pt.DataFields(1).Name).ShowDetail = True