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!

 

[Amaury_84]

Hi Jerry,

For some reason this did not work for me. It actually gave me the below error


-- removed inline image ---


was it something I did?

what I need to do is [FONT=&quot]Overwrite double click action in a PivotTable to go to actual source data.

for some reason this has really been difficult to do. I am no expert at all in VB.

I have copy and pasted all the code you had mentioned as well as added an Index column in my source data.

Please advise. [/FONT]

 

[Ausdave]

Hi - i've always wanted to solve the pivot - drill to details challenge. Anyway I literally just tried a new universal approach and it oh so satisfying. Maybe need improvement but please let me know if you see errors.
This assumes your pivot source is a table (which it should be!)

On the code tab for the worksheet with pivot table (this picks up the single click on data in the pivot table)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Cells.Count > 1 Or Target.Areas.Count > 1 Then Exit Sub
'check if we're in a pivot data cell
On Error GoTo bailout
If Target.PivotCell.PivotCellType = 0 Then
    On Error GoTo 0
    Call pvtDrillDown(Target.PivotCell)
End If

bailout:
End Sub

and put this in a code module wherever

Public Sub pvtDrillDown(pc As PivotCell)


Dim pvt As PivotTable
Dim pi As PivotItem
Dim lo As ListObject


Set pvt = pc.Parent
Set lo = Range(pvt.SourceData).Worksheet.ListObjects(pvt.SourceData)


lo.AutoFilter.ShowAllData


For Each pi In pc.RowItems
    lo.Range.AutoFilter Field:=lo.ListColumns(pi.Parent.Name).Index, Criteria1:=pi.Value
Next pi


For Each pi In pc.ColumnItems
    lo.Range.AutoFilter Field:=lo.ListColumns(pi.Parent.Name).Index, Criteria1:=pi.Value
Next pi


lo.Range.Worksheet.Activate


Set pvt = Nothing
Set pi = Nothing
Set lo = Nothing


End Sub

this wasn't intended to deal with slicers - if you have slicers just make sure the slicer field is in a row or column and yer good to go.
i guess filter fields would be a good add too...