When you’re analyzing data in an Excel pivot table, you might want to see the detail behind one of the numbers.
To extract the data, you can double-click a data cell and a new worksheet is created, with the related records.
This is a nice feature, but you’ll end up with extra sheets in your workbook, and will need to clean things up occasionally.
Filter the Source Data
If the pivot table source data is in the same workbook, you can use the following macro, written by Héctor Miguel Orozco Díaz. It filters the source data, based on the pivot items connected to the double-clicked cell.
For example, if you double-click the cell circled in screenshot below:
the source data is filtered for Class_A, Month_3, Store_1, Code_A cost.
This lets you focus on the detail records, without creating new worksheets.
Download the Sample File
Héctor’s code is shown on my Contextures site, and you can download the sample file to filter a pivot table’s source data.
There is also a sample file with a shorter version of the code.
________________
Debra
What a brilliant piece of coding by Héctor Miguel Orozco Díaz.
Please pass on my congratulations to him for writing this code and for his willingness to share this code through your site.
As a personal preference, I would like to be taken to the source data showing the filtered subset at the end of the routine, so I have added
Sheets(xSht).Activate
to the end of the main Sub in my copy.
Agreed. This is brilliant. I’m surprised the code is so long. I’ll be interested in stepping through it.
Thanks, Roger and Dick, I’ll pass along your comments. Hector also provided a shorter version of the code, I’ve now posted a link to that.
Deb
Hey,
this is brilliant code. I’m trying to use this on a file I’ve created that has 7 different pivot tables from one raw data set. I’m not so good with macros, but I’ve tried to simply copy in all my pviot tables and then paste my source data in the middle of his data, but the code keeps giving me an error. Any idea no how I can tweak the code to get it to work for me?
This code is exactly what I’ve been looking for, however I am unable to get it to work properly with my source data and pivot table. I keep getting a Runtime error ‘1004’: Application defined or object defined error at this line
With .RowRange: Set rowsF = Intersect(rowsD, .Resize(, .Columns.Count – lblFlds)): End With
Hi Tom
Whilst I am not the author of the code, I would be happy to take a look and see if I can figure what is going wrong.
Send me a copy of your file to
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address
Regards
Roger
could you help me im getting the same error
Tom,
Did you ever fix this problem? I am also getting the same error.
Ditto – getting the error that is .. any solution yet ?
I have used used this code. It is undoubtedly one of most brilliantly conceived code.
The problem mentioned by Keri and Uri are correct and I also faced the similar problem. I just added
On Error Resume Next
and it appeared to work well. At least it shows some filtered data. But later on I found that this way it does not show the correct filtered data.
On further analyzing i found that the code works perfectly when the data fields are placed as row heads but gives problem when the data fields are positioned as column heads.
To make my self more clear, if suppose the pivot is summing on two values ie field1 and filed2. Pivot can show the sum either as row head in which case the two cells “sum of field1” and “Sum of field2” appear one over another. They can also be placed one besides another and “sum of field1” and “Sum of field2” appear as column head.
The code works perfectly when the data fields are placed as row heads but gives problem when placed as column head. i am sure there is some small tweaking needed somewhere.
========
I was also thinking of another direct line of approach to the problem but because of very limited knowledge of VBA i find my self handicapped. I find get it is possible to get the fields and their values through a GETPIVOT function and then using activecell.formula . After having found these values in one go I feel splitting the range etc ( which is the approach taken in the code) may not be needed. I am working on that but as already mentioned because of limited knowledge of VBA I am getting stuck at every step. But logically I am sure it can be done. May be some of you may give it a try.
Regards
Dear All
I am very happy to inform you that with little tweaking I have been able to make the code work.
I have just added few line and made few changes and the code is working very fine. The entire code for PTCellFilterExcelDataSource is as follows. All other codes and instructions remain the same:
Good luck
S K Srivastava
=================
Hello Im gettin from this error = Sub or Function not defined , please HELP
Hello help now im getting the next error= slice , error no defined sub
This is absolutely wonderful. It is a shame Excel doesn’t come with this functionality built in. For years I have been using the following macros as a work around to add a ‘DELETE’ button every time a new page is created because I am double clicking on pivot tables all day long and I can easily add a 100 new pages in a day to a single spreadsheet. This little trick makes it so much easier to deal with them.
Add the following code to the “This Workbook” module under Microsoft Excel objects;
Then add the following macro to a regular module in the workbook
I actually have a whole suite of buttons that pop up with links to my favourite macros every time a sheet is added to a workbook. “Email this Page”, “Center Across” etc. They really save a lot of time.
Thanks to both H Diaz and S Srivastava for superb contributions!
I’ve been searching for exactly this function.
In order to get it work I had to make two changes, using Srivastava’s version:
1. The phrase:
If cpFilter "(All)" Then
generates a syntax error and should read:
If cpFilter <> "(All)" Then
2. I added a refresh of the pivot table source, which in my case is a table:
Worksheets(xSht).ListObjects(1).AutoFilter.ShowAllData
For a normal range, this code may do the same:
Worksheets(xSht).ShowAllData
@Mike.
I am glad that my contribution helped you.
It appears that you have been successful in making the code work for table source or listobject also. I am trying it for quite some time but have not been able to get it work so far. I request you to kindly post the code that works for table source or listobject . If there is some code that is generic and works for both named range and table then it is even better.
@ Debra
http://www.contextures.com/xlPivot-Filter-Source-Data.html#Code page says that
If your Excel pivot table source data is a list in the same workbook as the Excel pivot table, you can use the following macro, written by Héctor Miguel Orozco Díaz.
But the attached sample file does not appear to be doing so. Request you to please verify and post the correct sample file.
Regards
Hi Mike,
can you share the finalized code with me?
Hello did you get any working version of this?
OK so the 2 characters <> get removed on posting…
Code should be:
Thanks Mike — WordPress really doesn’t like those <> characters!
I’ve tried to fix it in the code samples below.