My code below works fine now, but I need help adjusting it. Instead of just updating PivotTable2, I also want to make the exact same updates to PivotTable6 (located on the same sheet). I tried a few things now, but no luck yet. Any help would be greatly appreciated!!
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Dim PvtTable As PivotTable, PvtItem As PivotItem
If (Intersect(Target, Range("B1")) Is Nothing) Then
Exit Sub
End If
'PivotTable to update
Set PvtTable = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable2")
'Clear all filters on the Job Number Field
PvtTable.PivotFields("Job Number").ClearAllFilters
'Turn off Application calculation and screen updating
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'Stop the PivotCache from recalculating until the selection change has been made
PvtTable.ManualUpdate = True
For Each PvtItem In PvtTable.PivotFields("Job Number").PivotItems
'If the current PivotItem caption isn't the same as the selection in cell B1
'Then set the Item to Visible=True, otherwise Visible=False and hide
PvtItem.Visible = (PvtItem.Caption = CStr(Range("B1").Value))
Next
ErrorHandler:
'Turn on Application calculation and screen updating
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'Turn the PivotCache calculation on again
PvtTable.ManualUpdate = False
End Sub
Bookmarks