Filtering Pivot Table with VBA

BriceChapman A couple of questions/comments:

1. Is the "Processing Area" field located in the Filters Area or the Rows Area of the pivot table?
2. Is the "Unable to set the Visible property of the PivotItem class" error occurring on the line of code that sets the Visible property to True or False?

If the error occurs when setting the Visible property to True, it's most likely because the Pivot Item no longer exists in the source table but has been retained as a Pivot Item due to the pivot table settings. One possible solution is to change this setting: right-click on the pivot table and select Pivot Table Options... then, on the Data tab, under "Retain items deleted from the data source", change the "Number of items to retain per field" from "Automatic" to "None". Then refresh the table to remove any pivot items that no longer exist.

Alternatively, the problem can also be avoided in this case by omitting the code to set the Visible property to True. Since the procedure begins by clearing all filters from the Pivot Field (pvFld.ClearAllFilters), you only need to change the Visible property of each applicable Pivot Item to False. You could also improve performance by only setting the property once at the end of the loop, if the Pivot Item was not found in the entire list:

Sub FilterPivotTableFromList()
    Dim vArray As Variant
    vArray = Worksheets("Deal Admin List").Range("D2:D4").Value

    Dim pvFld As PivotField, found As Boolean, i As Long, j As Long
    Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Processing Area")
    With pvFld
        .ClearAllFilters
        For i = 1 To .PivotItems.Count
            found = False
            For j = LBound(vArray, 1) To UBound(vArray, 1)
                If .PivotItems(i).Name = vArray(j, 1) Then
                    found = True
                    Exit For
                End If
            Next j
            If Not found Then .PivotItems(i).Visible = False
        Next i
    End With
End Sub

Having said that, the error may also occur when setting the Visible property to False, if NONE of the Pivot Items were found in the list, in which case the pivot table will not allow setting the Visible property of ALL Pivot Items in a Pivot Field to False. One lazy option would be to use On Error Resume Next, which would leave the last Pivot Item visible. Or you could write some additional code to handle this situation as follows:

Sub FilterPivotTableFromList()
    Dim vArray As Variant
    vArray = Worksheets("Deal Admin List").Range("D2:D4").Value

    Dim pvFld As PivotField, found As Boolean, n As Long, i As Long, j As Long
    Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Processing Area")
    With pvFld
        .ClearAllFilters
        For i = 1 To .PivotItems.Count
            found = False
            For j = LBound(vArray, 1) To UBound(vArray, 1)
                If .PivotItems(i).Name = vArray(j, 1) Then
                    found = True
                    n = n + 1
                    Exit For
                End If
            Next j
            If i = .PivotItems.Count And n = 0 Then
                .ClearAllFilters
                MsgBox "Unable to filter by the list of pivot items", _
                    vbExclamation, "No items found"
            ElseIf Not found Then
                .PivotItems(i).Visible = False
            End If
        Next i
        .EnableMultiplePageItems = True 'if located in the Filters Area of the pivot table
    End With
End Sub

Note: I also used .EnableMultiplePageItems = True in this last example, which may be required if the Pivot Field is located in the Filter Area of the pivot table.