Excel VBA Change Pivot Table Filters

ScuzzyJo
ScuzzyJo used Ask the Experts™
on
Hi Experts

Granted that I modified this from something  I found on the web, but can someone please tell me what is wrong with my code?

        With ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_status").ClearAllFilters
            ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_status").CurrentPage = loanStat
            ActiveSheet.PivotTables("PivotTable1").PivotFields ("loan_status")
            .PivotItems(loanStatus).Visible = True
        End With

I get Run-time error '1004':  Application-defined or object defined-error when it hits the second line.

loanStat is a variable which contains the text I want to filter loan_status on.

I'm awarding 500 points for a quick and working solution.

Thanks
Sarah
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015
Commented:
Rather use this code...

    Dim pt As PivotTable, pf As PivotField, pi As PivotItem

    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("loan_status")

    pf.ClearAllFilters

    For Each pi In pf.PivotItems

        If pi.Value <> loanstatus Then pi.Visible = False

    Next pi

Open in new window


Saurabh..

Author

Commented:
Absolutely fantastic!  Thanks :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial