VBA, Pivot Drill Down, Should be simple £5

  • Hi,


    As part of a wider project i need to drill into pivot table data and create the line item detail of the data required on a separate tab.


    I could just double click the table, but the table will be constantly updating and I want to automate this on each update.
    This will come later, and not part of this request, hence why only £5 offfered.


    What I would like guidance and code on is the following:



    • Code that automatically drills into the pivot table, can assign to a macro button for now (just a test)
    • Code that renames the sheet created based on a value in sheet1(Range A1:A1)


    My thoughts are that this should be simple and require only two or three lines .
    If this is not the case let me know and I will reconsider my approach.


    Thanks


    Matt:jumpupdo::jumpupdo:

  • Re: VBA, Pivot Drill Down, Should be simple £5


    Hello,


    Do you mean the functionality of double clicking a cell in the pivot table, to get an extra shett with the records for that 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: VBA, Pivot Drill Down, Should be simple £5


    Yes that is exactly what I want to do a complete detailed download of what the pivot table returns.


    The only variable will be the length of the pivottable, the fields will not change.


    Thanks


    Matt

  • Re: VBA, Pivot Drill Down, Should be simple £5


    Then why programming if the double click functionality is native in Excel ?

    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: VBA, Pivot Drill Down, Should be simple £5


    As it stands in isolation there is no reason.


    However the report loop you worked on earlier needs to accompanied by a pivot table download containing the detail.
    Rather than refresh a pivot table and double click 40 plus times i wanted to see if there was a way of making the creatpivot table a part of a macro.


    This is exploring ideas before putting out a full project at a sensible tendor price. This is one element of the functionality.


    I hope this explains my request.


    Thanks


    Matt

  • Re: VBA, Pivot Drill Down, Should be simple £5


    Hello Matt,


    Yes you can create a pivot table through code.
    Would it then be the aim to have a PT containing the detail, such that drilling down is not needed anymore ?
    Or the PT must be accompanied by a table showing the detail in a way that the user avoids to click 40 plus times ?
    I am not following you entirely I'm afraid.

    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: VBA, Pivot Drill Down, Should be simple £5


    H,


    I will try and clarfiy.


    The pivot table already exists and reads off a SAP (Financial ledger) system download.
    This download has about 40 cost centre in it.
    From this data I have both a report and a pivottable to is updated by changing a cell (I also have a loop thanks to your earlier help that will cycle through the 40 opions.
    What i need to do for each of the CC is drill into the pivot table and extract the details for that one cost centre (i.e. a list of individual cost items not summarised.


    Step 1
    At present I can drill into each pivot table one at a time, but it takes ages. What my ultimate goal is, is that i will run a macro and it will update the pivottable (already created) for each Cost centre collection and export the drill down info to a separate tab.


    Step2



    The next step is to automatically rename the created tab to the CC that is beibf exported i.e. "CC100001 Detail", then the next one, and the next one until I get to "CC100040 Detail".


    I hope this clarifys what i am trying to acheive, and thanks for your patience.

  • Re: VBA, Pivot Drill Down, Should be simple £5


    Do you mean the Show Pages... command to create separate worksheets for each Page field item.

    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: VBA, Pivot Drill Down, Should be simple £5


    Hi,


    I am not sure of the correct terminology, so i will attach screen shots of my sample workbook and what I am trying to do.


    [ATTACH=CONFIG]71144[/ATTACH]



    Step 1 is the sample data
    Step 2 is the Cost centre and Dates that will automatically update the pivot table
    Step 3 is the piviot fields being updated
    Step 4 is the drill down (double click that auto creates a new sheet.


    I have also attached the workbook so you can see the real thing.



    One additional point once I know the three separate points are viable I was gonig to put a design spec for an integrated pack out for tender.



    Hope this clears up what I am trying to do.


    Matt

  • Re: VBA, Pivot Drill Down, Should be simple £5


    Hello there,


    I wrote some code for you.
    Cost center and period are hardcoded and will need to be supplied within the loop over 40 cc.
    Please see attached file.


    Wigi

  • Re: VBA, Pivot Drill Down, Should be simple £5


    Hi Wigi,


    Had a tough day at work.


    I will review tomorrow and double check that i paid you corrrectly.


    Thanks in advance


    Matt

  • Re: VBA, Pivot Drill Down, Should be simple £5



    Good !


    Can you please transfer the funds as agreed upon ? Thanks.

    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: VBA, Pivot Drill Down, Should be simple £5


    Quote from Wigi;784427

    Good !


    Can you please transfer the funds as agreed upon ? Thanks.


    Funds received, thank you very much.

    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!