Pivot Filter Macro Help Requested

[jmischel]

I am trying to filter a field in a Pivot Table so that only the values 0 through 11 are visible. There may or may not be an instance of each value in each data set. If the value is present, I want it to be displayed. I have tried the following macro permutations but each has a problem:

ActiveSheet.PivotTables("Made2").PivotFields("Daysbetweenmakeandship"). _
ClearAllFilters

With ActiveSheet.PivotTables("Made2").PivotFields("Daysbetweenmakeandship")
.PivotItems("0").Visible = True
.PivotItems("1").Visible = True
.PivotItems("2").Visible = True
.PivotItems("3").Visible = True
.PivotItems("4").Visible = True
.PivotItems("5").Visible = True
.PivotItems("6").Visible = True
.PivotItems("7").Visible = True
.PivotItems("8").Visible = True
.PivotItems("9").Visible = True
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True

End With

The problem with the above method is that if there is no "1", for instance, the macro errors out.

The 2nd method I've tried is this:

ActiveSheet.PivotTables("Made2").PivotFields("Daysbetweenmakeandship"). _
PivotFilters.Add Type:=xlCaptionIsBetween, Value1:="0", Value2:="11"

This one was recorded with the recorder on the developer tab. The problem with this one is that it is treating the data values as text instead of numbers. If I go into the PivotTable and use the "between" filter, the data filters correctly. But when I record that as a macro, the type+ is xlCaptionIsBetween and that doesn't work correctly.

Thanks for any help provided.

 

[Greg Truby]

Try removing the quotation marks from around the values specified for args Value1 & Value2:

ActiveSheet.PivotTables("Made2").PivotFields("Daysbetweenmakeandship"). _
PivotFilters.Add Type:=xlCaptionIsBetween, Value1:=0, Value2:=11

 

[jmischel]

Greg,

Thanks - that solution solved my problem.

Jason

 

[Greg Truby]

Doncha love it when recorded code don't work.

Do be cognizant that the PivotFilter object is new @ 2007, so this code would crash if used on 2003 or lower.