Modify PivotTable double-click behavior --OR-- ...

gr8tday

New Member
Joined
May 16, 2014
Messages
14
Hello, I need some VBA code to address the following:

Scenario: A pivot table reveals a probable error in the underlying source data. The error needs to be located and corrected in the source data, and then the pivot table needs to be refreshed.

*Problem*: The double-click action on a pivot table fields creates a new worksheet with the records applicable to that field. A user must *identify the data error in the new worksheet and then scour the source data to find that same record*, correct the data error in the source, and refresh the pivot table.

One of two alternatives would make this process much easier for the user:

Option 1: Use VBA to modify the behavior of a PivotTable cell's double-click action to return the user to the source table, and automatically apply filters to the source data to display only those the records applicable to that field. (Assume the source data is already formatted as a Table.) In other words, don't create a new sheet with the applicable data, just filter the source data to show the applicable data. Then the user can find and correct the problem in one shot - in the source data.

This option would mean the user would not have to manually delete the new worksheet when they are done with it - always an annoyance. However, I don't want to affect the new worksheet functionality to achieve this goal; I've seen other code for the PivotTable double-click function affect this functionality.

Option 2: Allow the PivotTable double-click to work as normal. When the user finds the culprit record in the new worksheet, automatically take the user to that same record in the source table via a a macro short-cut key.

Thank you for any assistance you are able to provide!
 
IT WORKS!

I have no idea what was going on before, but I added message boxes to each Sub to try to track it down, and then it worked! Commented them back out, still works! Weird, but I'm happy!

Sorry for the false alarm!

Thank you so much, this code is AWESOME! YOU are awesome Jerry!

Kristie
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi Jerry,

I have one remaining issue: if a filter exists in the pivot source table, it is not cleared before filtering in the Index, so the drill fails to display any matching records. In the module code, the following exists but it does not clear the existing filter:
Code:
   With rPivotSource
      '--clear any existing filters
      .Parent.FilterMode = False

Any suggestions on how to correct/replace this line of code so that existing filters ARE cleared prior to the filtering for the pivot table drill?

Thanks!
Kristie
 
Upvote 0
Hi Kristie,

My mistake. That should read....

Code:
   With rPivotSource
      '--clear any existing filters
      .Parent.AutoFilterMode = False
 
Upvote 0
OK, I made the change but the problem still exists: If the source data is filtered by a user prior to any drills from the pivot table, the user filter is not removed prior to applying the pivot drill's filter. If a filter exists from a previous pivot table drill, then the first pivot drill filter is removed and the new drill filter is applied.
 
Upvote 0
OK, I made the change but the problem still exists: If the source data is filtered by a user prior to any drills from the pivot table, the user filter is not removed prior to applying the pivot drill's filter. If a filter exists from a previous pivot table drill, then the first pivot drill filter is removed and the new drill filter is applied.

Hmmm....it works in my mockup. :confused:

Could you provide an example file that exhibits that behavior? You could upload it to a sharing site like Box.com or send me a PM and we'll exchange email addresses.
 
Upvote 0
Kristie, Is your PivotTable DataSource an Excel Table or a standard range?

I hadn't considered the scenario of clearing existing filters from a Table, and in testing that, the code I suggested would exhibit the behavior you describe.

Here's a modified version (which is better that the previous one whether or not your source is a Table).

Code:
   With rPivotSource
      '--clear any existing filters
      On Error Resume Next
      .AutoFilter.ShowAllData
      On Error GoTo 0
      
      '--apply filter- split/join used to cast numbers as string
      .AutoFilter Field:=vUniqueIdCol, _
         Criteria1:=sFilterValues, _
         Operator:=xlFilterValues
      Application.Goto .Cells(1), True
   End With
 
Upvote 0
Excellent! That did the trick, it works flawlessly now! And yes, my source is an Excel Table, not a standard range. Thanks again!
 
Upvote 0
Is there a way to restrict or filter out fields after drilling down so that instead of the getting the entire table (in my case 21 fields) I can have 10 returned?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top