Multiple Items in Pivot Filter Controlled by Cell Reference Through VBA

[ankitgoel888]

Dear Friends,

I am facing problems in modifying a VBA code which helps me link a pivot table filter to a cell reference to allow multiple filters in the pivot table.

My code is as follows. I want to add both Expense and Income as a multiple filter to the pivot through a named range in the excel sheet. Please help.

I am fairly a newbie to VBA though making full effort to understand this as far as i can.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub


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



Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Category")
NewCat = Worksheets("Sheet1").Range("H6").Value



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

End Sub

 

[Oeldere]

crosspost

pivot, VBA