VBA- Change multiple pivot fields

[dinakar]

Hi,

I am trying to change the pivot filters in a worksheet for all the pivot tables. I tried the code below and it does not work. Can anyone please help! (Level 4,5, and 1 are my pivot fields)

[I]Sub Reset_filters()[/I]
[I]Dim pt As PivotTable[/I]
[I]For Each pt In Sheets("Rates Pivot").PivotTables[/I]
[I]    With pt.PivotFields( _
        "Level 4").ClearAllFilters
         pt.PivotFields( _
        "Level 4").CurrentPage = Sheets("Maps").Range("G2").Value
         pt.PivotFields( _
        "Level 5").ClearAllFilters
         pt.PivotFields( _
        "Level 5").CurrentPage = Sheets("Maps").Range("H2").Value
         pt.PivotFields("Cons Mgmt Location Level 1" _
        ).ClearAllFilters
         pt.PivotFields("Cons Mgmt Location Level 1" _
        ).CurrentPage = Sheets("Maps").Range("F2").Value
        
    End With[/I]
[I]Next pt[/I]
[I]End Sub[/I]

Thank you!

 

[cerfani]

"It does not work" is not enough info... How do you know it doesn't work? Does it crash? Does it complete with an unexpected result?

The first thing you can do is set a break point at the beginning of the macro and step through each line using F8. Then you can tell us here what exactly is broken, how you know it is broken and what it should do. The more detail your provide the better chance of getting a reply.

 

[dinakar]

Thank you for responding Cerfani! That helped, I checked step by step and I was able to figure what was breaking. The range I was referring to, didn't exist.

 

[cerfani]

ok cool, glad you figured it out. VBA is a horrible language to program in... in respects to the compiler reporting errors so sometimes you just have to step through it line by line. You can also use messageboxes to tell you what is going on or if a problem has happened in your code. Sometimes your code can navigate somewhere unexpected based on a typo. If you make a typo in a variable or something, vba will just create a new variant and assume you wanted a new variable. Anyways with VBA you have to be very careful and dont rely on the compiler pointing errors out.