Good day all,
Trying to filter a pivot table based on a dynamic range of cells, but keep on getting the same result in that the table only gets filtered on the first value and the rest is ignored. This will ultimately form part of a loop but for now I just want to get the filtering to work. Any assistance will be appreciated.
Trying to filter a pivot table based on a dynamic range of cells, but keep on getting the same result in that the table only gets filtered on the first value and the rest is ignored. This will ultimately form part of a loop but for now I just want to get the filtering to work. Any assistance will be appreciated.
VBA Code:
Option Explicit
Sub FilterPivotTable()
Dim filtvalues As Variant
Dim i As Integer, j As Integer
Dim pvt As PivotField
Dim pitm As PivotItem
filtvalues = Sheets("Mapping").Range("S7:W7")
Set pvt = Sheets("Recon By Business By Scheme").PivotTables("PivotTable28").PivotFields("Cognos code")
pvt.ClearAllFilters
For i = 1 To pvt.PivotItems.Count
Set pitm = pvt.PivotItems(i)
pitm.Visible = False
For j = 1 To UBound(filtvalues, 1) - LBound(filtvalues, 1) + 1
If pitm.Name = filtvalues(j, 1) Then
pitm.Visible = True
Exit For
End If
Next j
Next i
End Sub