Help tweaking vba to filter pivot based off range

[allstarrunner]

Hi, and thank you for your help,

This is the code I have (which I found on another forum, and it works great for calling on one value), but I need it adjusted so the Range “B3” can be called upon using a named range I set; or, in other words, to call on multiple values to filter in the pivot table, instead of just one i.e. B3:B8, then all 6 of those will show in the pivot table.
The error I get right now is a Type mismatch with the ‘filterCost’, which I assume has something to do with filterCost being defined as a String, but don’t really know. Thanks.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o>

</o:p>
Sub Apply_Cost_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterCost As String
 
Set pvtTable = Worksheets("Pivot_Sheet").PivotTables("PivotTable1")
Set pvtField = pvtTable.PivotFields("Cost")
 
filterCost = Worksheets("Controls_Sheet").Range("B3")
 
For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterCost Then
pvtField.CurrentPage = filterCost
Exit For
End If
Next pvtItem
<o:p></o:p>
End Sub
<o:p>

</o>

 

[Jerry Sullivan]

Hi allstarrunner,

Here's a function that you can call to filter a PivotField
to show only the items that are listed in an Item List.

The function is called using two required parameters:
ptField: The PivotField to be filtered
vItems: A Variant Array of the items to be Visible

The vItems parameter can be either an array or a range.

Public Function Filter_PivotField(ptField As PivotField, _
        vItems As Variant)
[COLOR="Teal"]'---Filters the PivotField to make stored vItems Visible[/COLOR]
    Dim sItem As String, bTemp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Not (IsArray(vItems)) Then
         vItems = Array(vItems)
    End If
 
    With ptField
        .Parent.ManualUpdate = True
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        If vItems(0) = "(All)" Then
            For i = 1 To .PivotItems.Count
                If Not .PivotItems(i).Visible Then _
                    .PivotItems(i).Visible = True
            Next i
        Else
            For i = LBound(vItems) To UBound(vItems)
                bTemp = Not (IsError(.PivotItems(vItems(i)).Visible))
                If bTemp Then
                    sItem = .PivotItems(vItems(i))
                    Exit For
                End If
            Next i
            If sItem = "" Then
                MsgBox "None of filter list items found."
                GoTo CleanUp
            End If
            .PivotItems(sItem).Visible = True
            For i = 1 To .PivotItems.Count
                If IsError(Application.Match(.PivotItems(i), _
                    vItems, 0)) = .PivotItems(i).Visible Then
                    .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
                End If
            Next i
        End If
    End With
    
CleanUp:
    ptField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

This is how the function could be called for the example you described.

Sub Example()
    Dim PT As PivotTable
    Set PT = Sheets("Pivot_Sheet").PivotTables("PivotTable1")
    
    Filter_PivotField ptField:=PT.PivotFields("MyFieldName"), _
        vItems:=Application.Transpose(Sheets("Controls_Sheet").Range("B3:B8"))
End Sub

You can replace Range("B3:B8") with a named range like: Range("MyRange")