Hello.
- I have a drop down cell (C2) on 'Sheet1' which contains a series of fixed values.
- I have a pivot table on 'Sheet2' called 'TestPiv' with a field 'Test' that corresponds to the value selected in Sheet1 cell C2.
- I would like the pivot table on Sheet2 to automatically be filtered based upon the value currently selected on Sheet1 cell C2.
Googling gave me the below, which I've tried to manipulate to my needs but haven't been successful. I've tried:
(a) adding the Sheet1 worksheet to the Intersect(Target... but learned that the target cell must be on the same sheet.
(b) creating a "helper" cell in cell A1 of Sheet 2 which references cell C2 of Sheet 1, but the pivot only updates if I physically type the value into A1.
Any help greatly appreciated!
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet2").PivotTables("TestPiv")
Set xPFile = xPTable.PivotFields("Category")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub
Bookmarks