Change a Pivot Table Filter according to a Cell Range

Question

I've tried many changes but can't make this work... Help plz :)

I want to change a pivot table filter according to a cell range.

Creating the sub inside the proper sheet (Graficos). Have tried renaming it to "Sheet6" and also the code.

Pivot Table Field: "Customer"

Pivot Table Name: "sellin"

Sheet: "Grafico"

Error 1004 on line 21: Field.CurrentPage = NewCat

Application-defined or object-defined error

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Intersect(Target, Range("F1:F2")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("Grafico").PivotTables("sellin")
Set Field = pt.PivotFields("Customer")

NewCat = Worksheets("Grafico").Range("F1").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

teylyn teylyn · Accepted Answer · 2016-07-12T21:33:02

Make sure that cell F1 has a value and that that value is one of the customer names. Then the code runs fine.

Change a Pivot Table Filter according to a Cell Range

1 Answers