Instead of using For Each loop, change the FilterArr to pivot reference and then you can use set the value of the .VisibleItemsList to your FilterArr. Here is an example.
FilterArr = Array( _
"[myTableName].[myPivotField].&[myPivotItem1]", _
"[myTableName].[myPivotField].&[myPivotItem2]")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[myTableName].[myPivotField].[myPivotField]").VisibleItemsList = FilterArr
This example assumes that you have a worksheet in the same workbook, with a table that contains the values for which the pivot table should be filtered. The pivot filter string for each filter value is created with a formula in the adjacent column and then read into an array. Additional variables are created with the values from named ranges.
Sub FilterPivot_WithListOfValues()
'---------------------------------------------------------------------------------------------------------
' Purpose: Dynamically create an array based on the values of one column of a table.
'
' Customize: Inputs in this sub come from 6 named ranges on a worksheet in this workbook.
' myPivotTableName, myPivotFieldName, mySheetName,
' myFilterTableName, smyDataTableName, myFilterCol
'
' Revisions:
' 09/30/19 Sub created
'
'---------------------------------------------------------------------------------------------------------
'~~~> Set the data types for the variables.
Dim oPT As PivotTable
Dim oPF As PivotField
Dim oPI As PivotItem
Dim strPT As String 'pivot table name
Dim strPF As String 'pivot field name
Dim strWS As String 'pivot table sheet name
Dim oWS As Worksheet
Dim oFTable As ListObject
Dim oDTable As ListObject
Dim strFTable As String 'filter table name
Dim strDTable As String 'data table name
Dim strFilterString As String 'pivot table filter string
Dim FilterArr() As Variant
Dim TempArray
Dim i As Long
Dim iCol As Long
'~~~> Assign the variables.
strPT = [myPivotTableName]
strPF = [myPivotFieldName]
strWS = [mySheetName]
strFTable = [myFilterTableName]
strDTable = [myDataTableName]
iCol = [myFilterCol]
'~~~> Build the filter string. Don't include the quotation marks wrapper.
' That is automatically added by .PivotFields
strFilterString = "[" & strDTable & "].[" & strPF & "].[" & strPF & "]"
'~~~> Check the variables in the Immediate Window.
Debug.Print "Variable Set: strPT = " & strPT
Debug.Print "Variable Set: strPF = " & strPF
Debug.Print "Variable Set: strWS = " & strWS
Debug.Print "Variable Set: strDTable = " & strDTable
Debug.Print "Variable Set: strFTable = " & strFTable
Debug.Print "Variable Set: iCol = " & iCol
Debug.Print "Variable Set: strFilterString = " & strFilterString
'~~~> Set the path for the table variable
Set oFTable = Range(strFTable).ListObject
'~~~> Set the path for the sheet variable
Set oWS = Worksheets(strWS)
'~~~> Create an array list from a table column.
TempArray = oFTable.DataBodyRange.Columns(iCol)
'~~~> Convert from vertical array to horizontal array list.
FilterArr = Application.Transpose(TempArray)
DisplayArrayValues:
'~~~> Loop through each item in the table array and display in Immediate Window [ctrl + g]
For i = LBound(FilterArr) To UBound(FilterArr)
Debug.Print FilterArr(i)
Next i
ApplyPivotFilters:
'~~~> Apply filters to the pivot table.
Set oPF = oWS.PivotTables(strPT).PivotFields(strFilterString)
With oPF
.VisibleItemsList = FilterArr
End With
ReleaseVariables:
'~~~> Release the variables from memory.
Set oPT = Nothing
Set oPF = Nothing
Set oPI = Nothing
Set oWS = Nothing
Set oFTable = Nothing
Set oDTable = Nothing
strPT = vbNullString
strPF = vbNullString
strWS = vbNullString
strFTable = vbNullString
strDTable = vbNullString
strFilterString = vbNullString
i = vbNull
iCol = vbNull
End Sub
This is a snip of the filter table and named ranges.
This is the formula that is used to generate the filter string.
="["&myDataTableName&"].["&myPivotFieldName&"].&["&[@[Pivot Items]]&"]"
