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]

Can anyone help please?

I have added an example sheet with Raw data and a Pivot Table. I've highlighted two cells on the PT and the corresponding coloured drill down tabs that are generated by double clicking the PT cell values.

Can this drill down be done with VBA?

If so, what would the code be to select various sources to drill down, based on other criteria?

Thank you VERY much for any advice!!!

 

[BSALV]

can you add the sourcetable (10-20 lines) with the XL2BB-tool ?
That works easier then a screenshot.

You want a kind of automatic drill down for for example type1, open, yes, moderate, dec 21 and prhaps the customer name on top ?
Every drill down creates a new sheet. Do you want that sheet to be copied to an existing one and the newly created sheet to be deleted ?

 

[MikexcelUK]

Hi there BSALV,

Thank you for replying.

I think I have done this correctly (I hope anyway).

This is my Raw Data:

Book1
	A	B	C	D	E	F	G	H	I
1	Customer Name	Type	SPAM	Auto	Reference	task.sys_created_on	Breach	task.priority	task.sys_class_name
2	Customer A	Open		Yes	Ref294715	########	TRUE	3	Type 1
3	Customer A	Open		Yes	Ref17516	########	FALSE	1	Type 1
4	Customer A	Open		Yes	Ref294719	########	TRUE	3	Type 1
5	Customer A	Open		Yes	Ref294713	########	TRUE	3	Type 1
6	Customer A	Open		Yes	Ref294711	########	TRUE	3	Type 1
7	Customer A	Open		Yes	Ref294716	########	TRUE	3	Type 1
8	Customer A	Open		Yes	Ref293835	########	FALSE	3	Type 1
9	Customer A	Open		Yes	Ref294714	########	TRUE	3	Type 1
10	Customer C	Open			Ref342616	########	FALSE	4	Type 2
11	Customer A	Open		Yes	Ref293832	########	FALSE	3	Type 1
12	Customer A	Open		Yes	Ref294717	########	TRUE	3	Type 1
13	Customer B	Open		Yes	Ref291222	########	FALSE	3	Type 1
14	Customer A	Open		Yes	Ref293833	########	FALSE	3	Type 1
15	Customer A	Open		Yes	Ref293830	########	FALSE	3	Type 1
16	Customer A	Open		Yes	Ref294718	########	TRUE	3	Type 1
17	Customer A	Open		Yes	Ref294712	########	TRUE	3	Type 1
18	Customer A	Open		Yes	Ref293829	########	FALSE	3	Type 1
19	Customer A	Open		Yes	Ref106361	########	FALSE	2	Type 1
20	Customer A	Open		Yes	Ref106382	########	FALSE	2	Type 1
21	Customer B	Open		Yes	Ref101263	########	FALSE	2	Type 1
22	Customer B	Open		Yes	Ref101261	########	FALSE	2	Type 1
23	Customer B	Open		Yes	Ref101262	########	FALSE	2	Type 1
24	Customer B	Open		Yes	Ref101264	########	FALSE	2	Type 1
25	Customer B	Open		Yes	Ref101286	########	FALSE	2	Type 1
26	Customer B	Open		Yes	Ref291253	########	FALSE	3	Type 1
27	Customer B	Open		Yes	Ref291445	########	FALSE	3	Type 1
28	Customer A	Open		Yes	Ref107588	########	TRUE	2	Type 1
29	Customer A	Open		Yes	Ref294049	########	TRUE	3	Type 1
30	Customer B	Open		Yes	Ref101304	########	FALSE	2	Type 1
31	Customer B	Open		Yes	Ref101296	########	FALSE	2	Type 1
32	Customer B	Open		Yes	Ref101294	########	FALSE	2	Type 1
33	Customer B	Open		Yes	Ref101306	########	FALSE	2	Type 1
34	Customer B	Open		Yes	Ref291252	########	FALSE	3	Type 1
35	Customer B	Open		Yes	Ref101279	########	FALSE	2	Type 1
36	Customer B	Open		Yes	Ref101278	########	FALSE	2	Type 1
37	Customer B	Open		Yes	Ref101265	########	FALSE	2	Type 1
38	Customer B	Open			Ref157178	########	FALSE	3	Type 1
39	Customer C	Open			Ref340754	########	FALSE	4	Type 2
40	Customer A	Open		Yes	Ref106386	########	FALSE	2	Type 1
41	Customer B	Open		Yes	Ref291218	########	FALSE	3	Type 1
42	Customer A	Open		Yes	Ref294727	########	TRUE	3	Type 1
43	Customer B	Open		Yes	Ref291246	########	FALSE	3	Type 1
44	Customer B	Open		Yes	Ref104311	########	FALSE	2	Type 1
45	Customer B	Open	Spam	Yes	Ref121124	########	TRUE	2	Type 1
46	Customer A	Open		Yes	Ref293827	########	FALSE	3	Type 1
47	Customer A	Open		Yes	Ref293828	########	FALSE	3	Type 1
48	Customer B	Open		Yes	Ref291227	########	FALSE	3	Type 1
49	Customer B	Open		Yes	Ref101270	########	FALSE	2	Type 1
50	Customer B	Open		Yes	Ref291449	########	FALSE	3	Type 1
51	Customer A	Open		Yes	Ref106384	########	FALSE	2	Type 1
52	Customer B	Open		Yes	Ref106489	########	TRUE	2	Type 1
53	Customer B	Open		Yes	Ref101260	########	FALSE	2	Type 1
54	Customer A	Open		Yes	Ref294729	########	TRUE	3	Type 1
55	Customer B	Open		Yes	Ref293870	########	TRUE	3	Type 1
56	Customer A	Open		Yes	Ref293841	########	FALSE	3	Type 1
57	Customer A	Open		Yes	Ref293844	########	FALSE	3	Type 1
58	Customer A	Open		Yes	Ref17518	########	FALSE	1	Type 1
59	Customer B	Open		Yes	Ref291241	########	FALSE	3	Type 1
60	Customer A	Open		Yes	Ref293849	########	FALSE	3	Type 1
61	Customer B	Open	Spam	Yes	Ref121099	########	FALSE	2	Type 1
62	Customer B	Open		Yes	Ref101297	########	FALSE	2	Type 1
63	Customer A	Open		Yes	Ref293964	########	TRUE	3	Type 1
64	Customer B	Open		Yes	Ref291448	########	FALSE	3	Type 1
65	Customer B	Open		Yes	Ref291450	########	FALSE	3	Type 1
66	Customer B	Open		Yes	Ref291231	########	FALSE	3	Type 1
67	Customer B	Open		Yes	Ref104305	########	FALSE	2	Type 1
68	Customer B	Open		Yes	Ref291229	########	FALSE	3	Type 1
69	Customer B	Open		Yes	Ref291230	########	FALSE	3	Type 1
70	Customer B	Open		Yes	Ref101277	########	FALSE	2	Type 1
71	Customer B	Open		Yes	Ref101276	########	FALSE	2	Type 1
72	Customer B	Open		Yes	Ref101275	########	FALSE	2	Type 1
73	Customer A	Open		Yes	Ref293836	########	FALSE	3	Type 1
74	Customer B	Open		Yes	Ref291232	########	FALSE	3	Type 1
75	Customer B	Open		Yes	Ref291228	########	FALSE	3	Type 1
76	Customer B	Open		Yes	Ref104304	########	FALSE	2	Type 1
77	Customer A	Open		Yes	Ref17992	########	TRUE	1	Type 1
78	Customer A	Open		Yes	Ref106372	########	FALSE	2	Type 1
79	Customer A	Open		Yes	Ref106373	########	FALSE	2	Type 1
80	Customer A	Open		Yes	Ref106375	########	FALSE	2	Type 1
81	Customer A	Open		Yes	Ref106377	########	FALSE	2	Type 1
82	Customer A	Open		Yes	Ref106379	########	FALSE	2	Type 1
83	Customer A	Open		Yes	Ref106376	########	FALSE	2	Type 1
84	Customer A	Open		Yes	Ref106374	########	FALSE	2	Type 1
85	Customer A	Open		Yes	Ref106378	########	FALSE	2	Type 1
86	Customer A	Open		Yes	Ref106380	########	FALSE	2	Type 1
87	Customer A	Open		Yes	Ref106371	########	FALSE	2	Type 1
88	Customer A	Open		Yes	Ref291497	########	FALSE	3	Type 1
89	Customer B	Open		Yes	Ref291243	########	FALSE	3	Type 1
90	Customer A	Open		Yes	Ref17539	########	FALSE	1	Type 1
91	Customer A	Open		Yes	Ref17515	########	FALSE	1	Type 1
92	Customer C	Open			Ref343727	########	TRUE	4	Type 2
93	Customer B	Open		Yes	Ref101287	########	FALSE	2	Type 1
94	Customer B	Open		Yes	Ref101312	########	FALSE	2	Type 1
95	Customer B	Open		Yes	Ref101274	########	FALSE	2	Type 1
96	Customer C	Open			Ref343725	########	TRUE	4	Type 2
97	Customer A	Open		Yes	Ref291542	########	FALSE	3	Type 1
98	Customer A	Open		Yes	Ref293826	########	FALSE	3	Type 1
99	Customer A	Open		Yes	Ref106391	########	FALSE	2	Type 1
100	Customer A	Open		Yes	Ref17526	########	FALSE	1	Type 1
101	Customer B	Open		Yes	Ref291220	########	FALSE	3	Type 1
102	Customer A	Open		Yes	Ref294724	########	TRUE	3	Type 1
103	Customer A	Open		Yes	Ref17991	########	TRUE	1	Type 1
104	Customer B	Open		Yes	Ref101303	########	FALSE	2	Type 1
105	Customer B	Open		Yes	Ref101310	########	FALSE	2	Type 1
106	Customer B	Open		Yes	Ref101292	########	FALSE	2	Type 1
107	Customer B	Open		Yes	Ref101302	########	FALSE	2	Type 1
108	Customer B	Open		Yes	Ref101295	########	FALSE	2	Type 1
109	Customer B	Open		Yes	Ref101252	########	FALSE	2	Type 1
110	Customer B	Open		Yes	Ref101301	########	FALSE	2	Type 1
111	Customer B	Open		Yes	Ref101289	########	FALSE	2	Type 1
112	Customer B	Open		Yes	Ref101299	########	FALSE	2	Type 1
113	Customer B	Open		Yes	Ref101291	########	FALSE	2	Type 1
114	Customer B	Open		Yes	Ref101305	########	FALSE	2	Type 1
115	Customer B	Open		Yes	Ref101298	########	FALSE	2	Type 1
116	Customer B	Open		Yes	Ref101251	########	FALSE	2	Type 1
117	Customer B	Open		Yes	Ref101288	########	FALSE	2	Type 1
118	Customer B	Open		Yes	Ref101282	########	FALSE	2	Type 1
119	Customer B	Open		Yes	Ref101290	########	FALSE	2	Type 1
120	Customer B	Open		Yes	Ref101300	########	FALSE	2	Type 1
121	Customer B	Open		Yes	Ref101308	########	FALSE	2	Type 1
122	Customer A	Open		Yes	Ref293840	########	FALSE	3	Type 1
123	Customer C	Open			Ref343210	########	FALSE	4	Type 2
124	Customer A	Open		Yes	Ref293995	########	TRUE	3	Type 1
125	Customer A	Open		Yes	Ref293851	########	FALSE	3	Type 1
126	Customer A	Open		Yes	Ref106389	########	FALSE	2	Type 1
127	Customer B	Open		Yes	Ref291236	########	FALSE	3	Type 1
128	Customer B	Open		Yes	Ref101284	########	FALSE	2	Type 1
129	Customer B	Open			Ref340965	########	FALSE	4	Type 2
130	Customer A	Open		Yes	Ref293845	########	FALSE	3	Type 1
131	Customer C	Open		Yes	Ref101975	########	FALSE	2	Type 1
132	Customer B	Open		Yes	Ref101309	########	FALSE	2	Type 1
133	Customer B	Open		Yes	Ref101307	########	FALSE	2	Type 1
134	Customer B	Open			Ref21203	########	FALSE	2	Type 1
135	Customer B	Open			Ref21277	########	TRUE	2	Type 1
136	Customer A	Open		Yes	Ref17996	########	TRUE	1	Type 1
137	Customer B	Open		Yes	Ref104297	########	FALSE	2	Type 1
138	Customer B	Open	Spam	Yes	Ref121100	########	FALSE	2	Type 1
139	Customer C	Open		Yes	Ref291406	########	FALSE	3	Type 1
140	Customer B	Open		Yes	Ref101271	########	FALSE	2	Type 1
141	Customer B	Open		Yes	Ref106786	########	TRUE	2	Type 1
142	Customer C	Open			Ref342427	########	FALSE	4	Type 2
143	Customer A	Open		Yes	Ref293848	########	FALSE	3	Type 1
144	Customer A	Open			Ref157837	########	FALSE	3	Type 1
145	Customer B	Open		Yes	Ref291239	########	FALSE	3	Type 1
146	Customer A	Open		Yes	Ref106385	########	FALSE	2	Type 1
147	Customer B	Open		Yes	Ref291209	########	FALSE	3	Type 1
148	Customer B	Open		Yes	Ref101254	########	FALSE	2	Type 1
149	Customer A	Open		Yes	Ref293847	########	FALSE	3	Type 1
150	Customer B	Open		Yes	Ref104309	########	FALSE	2	Type 1
151	Customer B	Open		Yes	Ref291240	########	FALSE	3	Type 1
152	Customer B	Open		Yes	Ref291221	########	FALSE	3	Type 1
153	Customer A	Open		Yes	Ref17524	########	FALSE	1	Type 1
154	Customer A	Open		Yes	Ref291548	########	FALSE	3	Type 1
155	Customer A	Open		Yes	Ref293961	########	TRUE	3	Type 1
156	Customer B	Open		Yes	Ref101269	########	FALSE	2	Type 1
157	Customer A	Open		Yes	Ref294721	########	TRUE	3	Type 1
158	Customer B	Open		Yes	Ref291244	########	FALSE	3	Type 1
159	Customer A	Open		Yes	Ref294065	########	TRUE	3	Type 1
160	Customer A	Open		Yes	Ref106383	########	FALSE	2	Type 1
161	Customer A	Open		Yes	Ref17517	########	FALSE	1	Type 1
162	Customer A	Open		Yes	Ref17995	########	TRUE	1	Type 1
163	Customer C	Open			Ref342613	########	FALSE	4	Type 2
164	Customer C	Open			Ref342087	########	FALSE	4	Type 2
165	Customer A	Open		Yes	Ref294708	########	TRUE	3	Type 1
166	Customer B	Open		Yes	Ref104302	########	FALSE	2	Type 1
167	Customer A	Open		Yes	Ref293825	########	FALSE	3	Type 1
168	Customer A	Open		Yes	Ref291736	########	FALSE	3	Type 1
169	Customer B	Open		Yes	Ref291444	########	FALSE	3	Type 1
170	Customer A	Open		Yes	Ref17993	########	TRUE	1	Type 1
171	Customer A	Open		Yes	Ref107577	########	TRUE	2	Type 1
172	Customer A	Open		Yes	Ref107570	########	TRUE	2	Type 1
173	Customer A	Open		Yes	Ref107571	########	TRUE	2	Type 1
174	Customer A	Open		Yes	Ref107574	########	TRUE	2	Type 1
175	Customer A	Open		Yes	Ref107576	########	TRUE	2	Type 1
176	Customer A	Open		Yes	Ref107575	########	TRUE	2	Type 1
177	Customer A	Open		Yes	Ref107569	########	TRUE	2	Type 1
178	Customer A	Open		Yes	Ref107572	########	TRUE	2	Type 1
179	Customer A	Open		Yes	Ref107573	########	TRUE	2	Type 1
180	Customer A	Open		Yes	Ref107568	########	TRUE	2	Type 1
181	Customer A	Open		Yes	Ref294000	########	TRUE	3	Type 1
182	Customer A	Open		Yes	Ref294031	########	TRUE	3	Type 1
183	Customer A	Open		Yes	Ref293842	########	FALSE	3	Type 1
184	Customer B	Open		Yes	Ref104298	########	FALSE	2	Type 1
185	Customer B	Open		Yes	Ref291446	########	FALSE	3	Type 1
186	Customer B	Open		Yes	Ref291452	########	FALSE	3	Type 1
187	Customer B	Open		Yes	Ref291454	########	FALSE	3	Type 1
Raw

This is my Pivot:

Customer Name	(All)
Count of Reference	Column Labels
	FALSE		FALSE Total	TRUE		TRUE Total	Grand Total
	2021	2021 Total		2021	2021 Total
Row Labels	Nov			Nov
	140​	140​	140​	43​	43​	43​	183​
Type 1	133​	133​	133​	41​	41​	41​	174​
Open	133​	133​	133​	41​	41​	41​	174​
	3​	3​	3​	1​	1​	1​	4​
2	1​	1​	1​	1​	1​	1​	2​
3	2​	2​	2​				2​
Yes	130​	130​	130​	40​	40​	40​	170​
1	7​	7​	7​	5​	5​	5​	12​
2	71​	71​	71​	13​	13​	13​	84​
3	52​	52​	52​	22​	22​	22​	74​
Type 2	7​	7​	7​	2​	2​	2​	9​
Open	7​	7​	7​	2​	2​	2​	9​
	7​	7​	7​	2​	2​	2​	9​
4	7​	7​	7​	2​	2​	2​	9​
Spam	2​	2​	2​	1​	1​	1​	3​
Type 1	2​	2​	2​	1​	1​	1​	3​
Open	2​	2​	2​	1​	1​	1​	3​
Yes	2​	2​	2​	1​	1​	1​	3​
2	2​	2​	2​	1​	1​	1​	3​
Grand Total	142​	142​	142​	44​	44​	44​	186​

 

[MikexcelUK]

Essentially what I want to happen is for each drill down to open a new sheet (like it normally would if I double click the value in the PT)... I can then take the new sheets and build a report that extracts itself to a separate workbook (I can do that myself).

 

[BSALV]

i hope you're not a complete rookie with VBA and can work with the macro-recorder.
Suppose you want a certain detail of your pivottable, start the macro-recorder, go somewhere outside the pivottable in a cell, click the "="-key and select that cell in the pivottable, you want the details of and enter.
You stop the macro-recorder and check what you got.
It' rather easy now
- delete the 2nd parameter of that formula, that refers to a cell of your pivottable
- delete every 2nd double quote character
- add a point in front and ".showdetail=true" at the end

Here some examples, from very simple to a little bit more complex.
The names i used here can be different in your situation, because perhaps my pivottable isn't 100% the same.
As you get more experience, in the end you can start using parameters outside that line and then start making macros with parameters, like the last examples

Sub Test()
     With Sheets("blad1").PivotTables(1)

     '    ActiveCell.FormulaR1C1 = "=+GETPIVOTDATA(""Reference"",R3C16)"
          Set c = .GetPivotData("Reference")                    'the grandtotal
          MsgBox c.Address                                      'just for demo, cell address
          c.ShowDetail = True                                   'show the detail
          ActiveSheet.Name = Format(Now, "hhmmss")              'you can give the new sheet a name and proceed here


     'ActiveCell.FormulaR1C1 = "=+GETPIVOTDATA(""Reference"",R3C16,""SPAM"",)"
          Set c = .GetPivotData("Reference", "SPAM", "Spam")    'subtotal for "Spam"
          MsgBox c.Address                                      'just for demo, cell address
          c.ShowDetail = True                                   'show the detail


     ' "=+GETPIVOTDATA(""Reference"",R3C16,""SPAM"",""Spam"",""task.sys_class_name"",""Type 1"")"
          .GetPivotData("Reference", "SPAM", "Spam", "task.sys_class_name", "Type 1").ShowDetail = True

     '"=+GETPIVOTDATA(""Reference"",R3C16,""SPAM"",,""task.sys_created_on"",2024,""Breach"",""FALSE"",""task.sys_class_name"",""Type 2"")"
          .GetPivotData("Reference", "SPAM", , "task.sys_created_on", 2024, "Breach", "FALSE", "task.sys_class_name", "Type 2").ShowDetail = True

     ' "=+GETPIVOTDATA(""Reference"",R3C16,""SPAM"",""Spam"",""task.sys_created_on"",2022,""Breach"",""FALSE"")"
          myspam = "Spam"                                       'now adding parameters to the "Getpivodata"
          mycreated = 2022
          mybreach = "false"
          .GetPivotData("Reference", "SPAM", myspam, "task.sys_created_on", mycreated, "Breach", mybreach).ShowDetail = True

     '    "=+GETPIVOTDATA(""Reference"",R3C16,""SPAM"",""Spam"",""Auto"",""Yes"",""task.sys_created_on"",2022,""Breach"",""FALSE"",""task.sys_class_name"",""Type 1"")"
          myauto = "yes"
          myclassname = "type1"
          .GetPivotData("Reference", "SPAM", myspam, "Auto", myauto, "task.sys_created_on", mycreated, "Breach", mybreach, "task.sys_class_name", myclassname).ShowDetail = True

     End With
End Sub

 

[BSALV]

i have it worked out for you, a macro with parameters, once that made, you only have to call him with the right values.

Sub Within_a_macro()

     'my 3 parameters
     myspam = "Spam"                                            'now adding parameters to the "Getpivodata"
     mycreated = 2022
     mybreach = "false"

     'first make a detail, direct = difficult to do so, without making errors, mistakes
     Sheets("blad1").PivotTables("draaitabel1").GetPivotData("Reference", "SPAM", myspam, "task.sys_created_on", mycreated, "Breach", mybreach).ShowDetail = True

     'second, do the same with a macro with parameters, easy, once done, you only have to provide the right parameters
     ShowDetail_1 Sheets("blad1").PivotTables("draaitabel1"), myspam, mycreated, mybreach

End Sub

Sub ShowDetail_1(PVT As PivotTable, my_spam, my_created, my_breach)
     PVT.GetPivotData("Reference", "SPAM", my_spam, "task.sys_created_on", my_created, "Breach", my_breach).ShowDetail = True
End Sub

 

[MikexcelUK]

I think this is EXACTLY what I am looking for. I had seen the getpivotdata in VBA before, but I thought that was just working the same way as the formula to reference a value. I will work on this in the morning.

THANK YOU VERY MUCH!!!!!!!!!!

 

[MikexcelUK]

I couldn't wait until tomorrow. I have done some initial testing and I totally get it now.

Thank you again so much for your help. This will save me so much time!!!!!!!

 

[AmitSatnalika]

Hi Mikexcel ,
can i get the complete working code for this , i am in great need for this...
you can also post the file with code

thanks in Advance.