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)

 

[kcmuppet]

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.

I'm not sure what you mean. The approach earlier in the other thread works without building up an array. Is that only because its not an OLAP source? (Actually I don't know what an OLAP source is)

Are you able to provide an example of how I should do it?

 

[RoryA]

Which approach specifically? Currentpage will work (that only works for page/filter fields) because you are specifying one specific item. Looping through pivotitems will also work because you are again specifying exact items. For a filter field, the option to filter for things like 'After today' or 'Next year' are not available, unlike for row/column fields. You only have the option to select one or more specific items within a list.

 

[kcmuppet]

Which approach specifically?

This one:

    Dim pt As PivotTable
    For Each pt In Sheet5.PivotTables
        With pt.PivotFields("Date")
            .ClearAllFilters
            .PivotFilters.Add Type:=xlSpecificDate, Value1:=Format(Range("D7").Value, "m/d/yyyy")
        End With
    Next pt

For a filter field, the option to filter for things like 'After today' or 'Next year' are not available

I'm not trying to use those. I'm trying Type:=xlAfterOrEqualTo, and Type:=xlBeforeOrEqualTo. When I looked up Type:=xlSpecificDate here I found those two as XlPivotFilterType enumeration options too, so assumed I could use those instead of Type:=xlSpecificDate.

The reason I'm trying to do all this to to make sure the charts on the sheet all match the timescales given between two (changeable) dates from a couple of cells, which can be years apart.

If I understand you correctly It would require the VBA code to identify each day between the two dates and and specifically select each of the days across those years. Is that right?

Or is there more efficient approach than I have started upon?

 

[RoryA]

Are you saying you can make that Pivotfilters.Add line work with a filter field?

 

[kcmuppet]

Are you saying you can make that Pivotfilters.Add line work with a filter field?

Having now understood what you mean, no I can't. I can make it work if the date field is in the Row section like this:

...with something like this:

Sub Macro2()
    ActiveSheet.PivotTables("PivotTable2").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFilters.Add Type:=xlDateBetween, Value1:=Format(Range("T3").Value, "dd mmm yyyy"), Value2:=Format(Range("T4").Value, "dd mmm yyyy")
End Sub

or this:

Sub Macro1()
Dim d1 As String, d2 As String
d1 = CStr(Range("R3"))
d2 = CStr(Range("R4"))
   
    ActiveSheet.PivotTables("PivotTable2").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFilters.Add Type:=xlDateBetween, Value1:=d1, Value2:=d2

End Sub

You'd need to loop through the pivotitems

Do you mean looping like this?:

Sub LoopThruAllDatesMethod()

Dim pt As PivotTable
Dim d1 As Date, d2 As Date
Dim pi As PivotItem

d1 = Range("T3").Value
d2 = Range("T4").Value

Set pt = ActiveSheet.PivotTables("PivotTable2")

With pt.PivotFields("Date")
    .ClearAllFilters
    For Each pi In .PivotItems
            If pi.Name < d1 Then
                pi.Visible = False
            End If
            If pi.Name > d2 Then
                pi.Visible = False
            End If
    Next
End With
End Sub

That approach seems to work for a pivot table whose source is a regular Excel Table (but takes a long time with a lot of dates).
When I try to apply that to a pivot table whose source is from a table in power pivot data model, I run into some trouble. First .PivotFields("Date") doesn't work, so I replace with .CubeFields("[q_Events].[Date]"). Then I get a compile error at .ClearAllFilters. If I remove that, it stops at .PivotItems.

I'm stuck