'Filter Pivot Table based on clicked cell text
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?
Solution 1:[1]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Shai Rado |