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

[gr8tday]

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!

 

[gr8tday]

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

 

[Jerry Sullivan]

I'm glad you sorted that out.

 

[gr8tday]

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:

   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

 

[Jerry Sullivan]

Hi Kristie,

My mistake. That should read....

   With rPivotSource
      '--clear any existing filters
      .Parent.AutoFilterMode = False

 

[gr8tday]

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.

 

[Jerry Sullivan]

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.

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.

 

[Jerry Sullivan]

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).

   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

 

[gr8tday]

Excellent! That did the trick, it works flawlessly now! And yes, my source is an Excel Table, not a standard range. Thanks again!

 

[David Wayne]

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?

 

[Jerry Sullivan]

Hi David,

There's some code in this thread that will do that. Use the code in Post #40 and make the correction noted in Post #42.

http://www.mrexcel.com/forum/excel-...-columns-range-instead-table.html#post3705211