Hi,

I have a list which I need to filter for within a pivot table. The issue is that there are 101 values and it takes an age to manually select each for the filter not to mention the worry of incorrect selections.

So I've tried to amend some code found online but I've never had any joy with Autopopulation of Pivot Table filters so I could do with some help please.

Thanks in advance


Sub ADH468()

Dim range1 As Range
Set range1 = Sheets("ADH468 Pilot List").Range("B2:B102")
Dim var1 As Variant
Dim sArray() As String
Dim i As Long
var1 = range1.Value

ReDim sArray(1 To UBound(var1))

For i = 1 To (UBound(var1))
    sArray(i) = var1(i, 1)
Next

ActiveSheet.PivotTables("PivotTable5").PivotFields ("ATM2"), Criteria1:=sArray, Operator:=xlFilterValues

End Sub