Hi,
I have used the pivot table filter based on one cell changing, but I would now like to filter a pivot table in a different file based on whether two different ranges have changed. I have altered the code I had in my original file, combined it with another few tips I have found from searching the site but don't appear to be having much luck. The target ranges are on a different worksheet to the pivot table, I have the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim R1 As Range
Dim R2 As Range
Set R1 = Range("Dash!C2")
Set R2 = Range("Dash!C5:E5")
If Application.Intersect(Target, Union(R1, R2)) Is Nothing Then Exit Sub
With ActiveSheet.PivotTables("CSL Graph Pivot").RefreshTable
With ActiveSheet.PivotTables("CSL Graph Pivot").PivotFields("Week2")
.PivotItems("").Visible = False
.PivotItems("(blank)").Visible = False
With ActiveSheet.PivotTables("CSL Shorts Pivot").RefreshTable
With ActiveSheet.PivotTables("CSL Shorts Pivot").PivotFields("Week2")
.PivotItems("").Visible = False
.PivotItems("(blank)").Visible = False
End With
End With
End With
End With
End Sub
Can anyone help?
Thanks
Lauren
I have used the pivot table filter based on one cell changing, but I would now like to filter a pivot table in a different file based on whether two different ranges have changed. I have altered the code I had in my original file, combined it with another few tips I have found from searching the site but don't appear to be having much luck. The target ranges are on a different worksheet to the pivot table, I have the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim R1 As Range
Dim R2 As Range
Set R1 = Range("Dash!C2")
Set R2 = Range("Dash!C5:E5")
If Application.Intersect(Target, Union(R1, R2)) Is Nothing Then Exit Sub
With ActiveSheet.PivotTables("CSL Graph Pivot").RefreshTable
With ActiveSheet.PivotTables("CSL Graph Pivot").PivotFields("Week2")
.PivotItems("").Visible = False
.PivotItems("(blank)").Visible = False
With ActiveSheet.PivotTables("CSL Shorts Pivot").RefreshTable
With ActiveSheet.PivotTables("CSL Shorts Pivot").PivotFields("Week2")
.PivotItems("").Visible = False
.PivotItems("(blank)").Visible = False
End With
End With
End With
End With
End Sub
Can anyone help?
Thanks
Lauren