Help tweaking vba to filter pivot based off range

allstarrunner

New Member
Joined
May 29, 2012
Messages
34
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p>
Rich (BB code):
Rich (BB code):
Rich (BB code):
</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:p>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.

Code:
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.
Code:
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")
 
Upvote 0

Forum statistics

Threads
1,211,710
Messages
6,103,450
Members
447,866
Latest member
bowers261

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top