Using VBA to get drill down data from a pivot table

[MikexcelUK]

I am OK working with some VBA, so I can usually squash together different code to get what I need. I am no way an expert and usually rely on forums to find code that does what I need. I have searched for a solution to this and cant find anything...

I have a table of customer data (different utilities, usage, and amount some other fields) that is added to on a monthly basis. I edit the data through a pivot table and then using slicers which allow a particular customer, year and the month to be selected to return that month’s results (using getpivotdata formulas) - the selection of the month/year is to check prior months and trends. There are many different outputs I need, and I can get all of these using the getpivotdata formulas linked to cells (to make them dynamic) and put these into graphs and trends for people in my team to use to populate reports easily. These reports are on a linked worksheet, to the Pivot Table and working sheets are hidden.

The outputs have several fields which are volatile per customer (some may have more or less data (or none) each month). As a result, the pivot table size changes per customer selection (plus it gets wider each month).

Here is what I need:

What I would also like to be able to do is provide that month's underlying data for the chosen customer, plus the data per individual field, if/when that is needed for analysis.

Example: On the report page that is visible, I would have a button to download that month’s power data, another for the water data, one for the combined data, etc. All the drill down data can be selected by double clicking the appropriate cell in the Pivot Table. I can't record anything, because the recorded macro just refers to a cell reference, which changes with each customer selection and month (I only want this for the most recent month).

My thoughts are that I can have control cells automatically update with information pertaining to the pivot table headers and then have the code look at those cells to determine the drill down locations on the pivot table and then to extract that into a new sheet as usual.

I have searched high and low and cannot find anything to help… any ideas? Is this even possible?

 

[MikexcelUK]

Hi Amit,

I cannot share my file, because it contains real data - sorry.

If you review BSLAV's reply, that will explain how it works... essentially, if you use the =getpivot formula, then remove the redundant inverted commas (") in the VBA code, then it works

 

[AmitSatnalika]

can't you share with the dummy data , meaning you can just alter your data with any abcd....z and then pivot macros should work as it is

 

[AmitSatnalika]

Also there are two codes from bsalv which one to use.

 

[MikexcelUK]

Also there are two codes from bsalv which one to use.

You need to work it out for what you want to use. Her code is just guidance...

 

[Fluff]

Duplicate to: Auto Drill Pivot table to Raw base data instead of creating new worksheet

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.