1

So, I've written some VB to filter a pivot table based on a range. 

Worksheets("Collections By Timekeepers").PivotTables("Collections By Timekeepers").PivotFields(1).VisibleItemsList = arrFilters

Now I need to do the same but with HiddenItemsList, however, I'm getting a 1004 "Application-defined or object-defined error" despite being an OLAP cube.

Worksheets("Collections By Timekeepers").PivotTables("Collections By Timekeepers").PivotFields(2).HiddenItemsList = arrFilters

Is there a work around, or another way to exclude items?

user1093111
  • 1,091
  • 5
  • 21
  • 47

1 Answers1

1

You need to set .CubeField.IncludeNewItemsInFilter to True in order to do this. Suggest you check out my two recent answers on this:

This answer gives you the syntax: Pivot Table filter out only 1 option

Note that you can't use this method on PageFields. But here's a workaround: This answer gives you a function to filter OLAP PivotTables no matter what kind of field they are: how to set PivotField.HiddenItemsList property's value if CubeField.Orientation = xlPageField

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27