VBA, PivotTable, Drill DOwn

  • Hi,

    I have a pivot table that needs regular updating, and I am drilling down into the pivottable data by double clicking the grand total at the bottom.
    Instead I would like to assign this as part of a macro.

    • Think i need to use the pivot table method, but can not find anything complete to play with, I suspect this is really simple.
    • Assume the pivotable is in sheet one and is called Pivottable1

    Code I have tried:

    Worksheets("Sheet1").PivotTables("PivotTable1").ShowDetail = True

    Please can someone advise me of how to use VBA to automate this with a macro?

    Thanks in advance


  • Re: VBA, PivotTable, Drill DOwn

    Hi Matt- so what exactly are you trying to do? When you click anywhere in a pivot table you see all the data behind it?


  • Re: VBA, PivotTable, Drill DOwn


    Put I want a macro to do that for multiple pivot tables all at once.

    I thought I would keep it simple by using one as an example.


  • Re: VBA, PivotTable, Drill DOwn

    Hey Matt-

    So this is what i came up with, it aint pretty and assumes that the grand total cell you want to double click on is in the bottom right cell of the pivot table, but it works- see below:

    Hope this helps,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!