Automatically pick up showdetail field of pivot table, £5

  • Hi


    I have the below VBA that works with a pivottable of a fixed number of rows.


    What i need is code that will allow drilldown, show detail cell to be selected on a pivot table of any length.
    Currently the VBA is set to cell "I33".


    I am assuming that this is a relatively common vba request





    Thanks


    Matt

  • Re: Automatically pick up showdetail field of pivot table, £5


    Hello, I will do this one for you.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Automatically pick up showdetail field of pivot table, £5


    Quote from Wigi;784527

    Hello, I will do this one for you.


    Agreed and thanks.

  • Re: Automatically pick up showdetail field of pivot table, £5


    Hello,


    Here is a sample file, can you evaluate it and adjust the coding if needed ? Or ask me.

  • Re: Automatically pick up showdetail field of pivot table, £5


    Hi,


    This does not really do what I am asking, because I will not be able to automate the selection.


    In this model column E will always have the pivot table total that I wish to drill into, the row however will change.
    I tried to use the offset function to pick up the grand total in the pivot table and return the correct cell, but could not get it to work.


    Does this make sense and is what I am asking feasible?

  • Re: Automatically pick up showdetail field of pivot table, £5


    Is your pivot table always 1 cell ?

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Automatically pick up showdetail field of pivot table, £5


    My pivot will be as follows:


    Filtered by cost centre and month
    Columns = Total
    Rows = Account codes


    As different cost centres have different activity the number of rows in each cost centre and hence length of pivot table will constantly change.


    That will be the only dynamic variable.


    Hope this clarifies.

  • Re: Automatically pick up showdetail field of pivot table, £5


    Where do I see account codes in your data table, A6:C15 ?

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Automatically pick up showdetail field of pivot table, £5


    Updated coding:


    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Automatically pick up showdetail field of pivot table, £5


    It's not in the mock up i sent you.
    If you want me to send a live file I will have to get some data from work and clense it/ tyle over, so that it has nothing confidential in.


    If you need this it i can't access the data until tomorrow night.
    That said it could be any pivot table where the row changes and the column is fixed.

  • Re: Automatically pick up showdetail field of pivot table, £5


    My latest update should enable the drill on the variable number of rows, did you test it ?

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Automatically pick up showdetail field of pivot table, £5


    Quote from Mattador;784548

    Yes, but it only works if the bottom cell is manually selected.


    No because there is no manual selection:


    pt.DataBodyRange.Cells(pt.DataBodyRange.Cells.Count).ShowDetail = True

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Automatically pick up showdetail field of pivot table, £5


    Hi,


    I think we are missing each others point.


    To help find common understanding I will change how I explain my position.


    To help I have attached a fresh work book with illustrated notes and a screen shot for visual reference.



    Screen Shot



    [ATTACH=CONFIG]71219[/ATTACH]




    forum.ozgrid.com/index.php?attachment/71220/


    As the cell highlighted red will move up and down depending on the number of Gl codes in a cost centre I need code that automatically selects the grandtotal drilldown field.


    I thought I had communicated this clearly, obviuosly not. If you feel this is moving the goal posts then let me know and we can come to some amicable arrangement.


    Thanks


    Matt

  • Re: Automatically pick up showdetail field of pivot table, £5


    Hello,


    I see why you are still saying that it only works if you select the grand total cell manually. That's because initially we had the line of code:


    Code
    ActiveCell.ShowDetail = True


    But after that I changed it to:


    Code
    pt.DataBodyRange.Cells(pt.DataBodyRange.Cells.Count).ShowDetail = True


    If I do that change again in your latest file, it just works fine.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Automatically pick up showdetail field of pivot table, £5


    Perfect works as requested.


    Funds transferred, let me know if not received.


    Matt

  • Re: Automatically pick up showdetail field of pivot table, £5


    You're welcome ! Funds arrived :)

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

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