VBA Pivot Table filters based on two ranges of cells

[lminsh]

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

 

[Worf]

Welcome to the Board

Here is an example:

' this is the module for a worksheet named "software"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng1 As Range, pt As PivotTable, rng2 As Range
Set pt = Sheets("tablets").PivotTables("pivottable2")   'another sheet
Set rng1 = [e1:e5]
Set rng2 = [f1:f5]
If Application.Intersect(Target, Union(rng1, rng2)) Is Nothing Then Exit Sub
MsgBox "Filtering tablet sheet...", 64, "This is " & Me.Name & " sheet"
pt.PivotFields("category").PivotFilters.Add2 Type:=xlValueIsBetween, _
DataField:=pt.PivotFields("Sum of amount"), Value1:=80, Value2:=100
End Sub

 

[lminsh]

Hi,

Thank-you very much for the welcome nd code example, one question- can the values for the filter be cells on the sheet? Or do they need to be typed into the code and remain static?

 

[lminsh]

I have cracked that bit by just adding range, but now getting a run time error 5 - invalid procedure call or argument on the last line, it's the field I would like to filter, not the values coming up.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng1 As Range, pt As PivotTable, rng2 As Range
Set pt = Sheets("CS & Shorts").PivotTables("CSL Shorts Pivot")
Set rng1 = [C2:C3]
Set rng2 = [C5:f6]
If Application.Intersect(Target, Union(rng1, rng2)) Is Nothing Then Exit Sub
pt.PivotFields("Week2").PivotFilters.Add2 Type:=xlValueIsBetween, _
DataField:=pt.PivotFields("Week2"), Value1:=Range("I2"), Value2:=Range("J2")
End Sub

 

[Worf]

What kind of pivot field is it? Column, row, value?

 

[lminsh]

What kind of pivot field is it? Column, row, value?

It has two columns, I figured it out using your sugestion combined with another thread, i just took out the 'datafield' and filtered the values of the column with xlcaptionisbetween

Thank-you very much for your help

Lauren