Pivot Table CubeFields. How to a refer properly in VBA

[kcmuppet]

Hi I'm trying to use VBA to filter pivot tables on a sheet by a date field from a data model, using a date range referenced in cells on the sheet,
The code here

VBA Code:
Sub Filter_PT_Date_Range()
Dim pt As PivotTable

   For Each pt In Sheet2.PivotTables
      With pt.CubeFields("[q Events].[Event Date]")
         .ClearAllFilters
         .PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=Format(Range("T3").Value, "dd mmm yyyy")
         .PivotFilters.Add Type:=xlBeforeOrEqualTo, Value1:=Format(Range("T4").Value, "dd mmm yyyy")
      End With
   Next pt
End Sub

... gives me a compile error at .ClearAllFilters. Is there something wrong with the way I'm referencing the CubeFields field?

(This is also posted on an old thread which started on another topic, here)

 

[RoryA]

A cubefield object doesn't have a clearallfilters method or a Pivotfilters property. Did you try just using Pivotfields instead?

 

[kcmuppet]

Hi Rory. Thanks for your reply.

Yes I did, but it didn't work, so I recorded macro while manually filtering and that gave me the CubeFields reference, but not having seen it before, I'm not sure how it needs to be handled.

 

[RoryA]

If it's a report filter field, you can't actually do this (manually or in VBA). You can only select specific items, not apply filters.

 

[kcmuppet]

If it's a report filter field, you can't actually do this (manually or in VBA). You can only select specific items, not apply filters.

Not sure quite what you mean by report filter field, but it works ( along the lines of the other posts in that thread) with one date and if I filter using a field that isn't in the data model (ie. just from a regular Excel Table), but not with the way I have tried to reference the field in the data model

 

[RoryA]

Where is the field in the pivot table?

 

[kcmuppet]

I have a Table called tbl_Events, with a field "Event Date", which is the source of PowerQuery called "q Events" that adds some manipulation and loads as a "connection only" added to the data model

 

[RoryA]

That doesn't answer my question. Whereabouts in the pivot table is the field you are trying to filter? Row area, column area, filter area?

 

[kcmuppet]

That doesn't answer my question. Whereabouts in the pivot table is the field you are trying to filter? Row area, column area, filter area?

Oh I see, it's in the 'Filters' sections of 18 pivot tables charts on the same sheet

 

[RoryA]

In the filter area, you have to specify the actual values you want - you can't use filters like the ones you were trying in your code. You'd need to loop through the pivotitems and build up an array of the items you want, which you can then assign to the visibleitemslist property since it's an OLAP source.