Filter Pivot Table based on clicked cell text

Question

My workbook consists of 2 sheets: Sheet1 is the primary dashboard that the user sees, while Sheet2 consists of a pivot table and regular table.

I'd like to implement some VBA code so that the user can click on some text in Sheet1 and the pivot table on Sheet2 will filter according to what text the user clicked on.

So for instance, Column A on Sheet1 is a list of US Cities. I'd like the user to be able to double click on a city name in Sheet1 and have the pivot table on Sheet2 filter to whatever the city name was clicked on.

I don't need the macro to actually bring the user to Sheet2. All I'm planning on doing is just referencing the pivot table on Sheet1.

I designed something similar using a standard table, but I really need this to work on the Pivot Table and I'm not sure how to go about it.

UPDATE

Ok so here is my code now:

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("E:P")) Is Nothing Then
    Application.ScreenUpdating = False
    Worksheets("Dillon Pivot").PivotTables("PivotTable2").ManualUpdate = True
    Worksheets("Dillon Pivot").PivotTables("PivotTable2").PivotFields("Match").ClearAllFilters
    Worksheets("Dillon Pivot").PivotTables("PivotTable2").PivotFields("Match").CurrentPage = ActiveCell.Value
    Worksheets("Dillon Pivot").PivotTables("PivotTable2").ManualUpdate = False
    Application.ScreenUpdating = True
  End If
End Sub

I want the user to be able to click a cell on the main dashboard and have the pivot table on "Dillon Pivot" be filtered.

However, when I double click a cell I get the following error: "Application-defined or object defined error"

Can anyone assist?

Take a look at the following - it describes how to replace a filter: http://stackoverflow.com/questions/11071662/filter-excel-pivot-table-using-vba Then you just need the code to handle your Double-Click, and that can be found here: http://www.excel-easy.com/vba/examples/beforedoubleclick-event.html Any further help needed, let us know. — Wayne G. Dunn, Nov 25 '16 at 19:43
Why not use [Slicers](https://support.office.com/en-us/article/Use-slicers-to-filter-PivotTable-data-249f966b-a9d5-4b0f-b31a-12651785d29d)? — Parfait, Nov 26 '16 at 01:16
@Darren have you tested any of the solutions provided here ? a response for people who made an effort to help you ? — Shai Rado, Dec 06 '16 at 09:06

score 0 · Answer 1 · answered Nov 26 '16 at 12:21

Add the piece of code below to your "Sheet1" events.

The code inside the Intersect is triggered, only if the cell that was double-clicked is inside Column A (until last row that has a name of a city inside).

I use a variable Dim PvtTbl As PivotTable to set the "PivotTable2" in "Dillon Pivot" sheet - it makes the code much shorter and easier to understand.

Code

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim LastRow As Long
Dim PvtTbl  As PivotTable

' find last row in Column A that has a city
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

' check if double-click was inside a range in Column A, where there is a city name
' starting from row 2, assuming the first row is the header row
If Not Intersect(Target, Range("A2:A" & LastRow)) Is Nothing Then
    Application.ScreenUpdating = False

    Set PvtTbl = Worksheets("Dillon Pivot").PivotTables("PivotTable2")

    With PvtTbl
        .ManualUpdate = True
        .PivotFields("Match").ClearAllFilters
        .PivotFields("Match").CurrentPage = Target.Value
        .ManualUpdate = False
    End With
    Application.ScreenUpdating = True
End If

End Sub

Filter Pivot Table based on clicked cell text

1 Answers1