Pivot Table Data

[LutonBarry]

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.

[Aflatoon]

Assuming a pivottable object variable pt:

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

[p45cal]

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!

[LutonBarry]

Aflatoon,

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.

[Aflatoon]

Dim pt as Pivottable
set pt = activesheet.pivottables("PivtTable6")

for example.

[LutonBarry]

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.

[LutonBarry]

p45cal That's great it works a treat. How does it work though and suppose I wanted to take say the leftmost total?

[p45cal]

leftmost total of the values/Data area of the pivot:

pt.GetPivotData(pt.DataFields(1).Name).ShowDetail = True