Pivot Table CubeFields. How to a refer properly in VBA

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010
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:
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)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
A cubefield object doesn't have a clearallfilters method or a Pivotfilters property. Did you try just using Pivotfields instead?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Where is the field in the pivot table?
 

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Nov 2, 2005
Messages
315
Office Version
  1. 365
  2. 2010
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,136,275
Messages
5,674,782
Members
419,524
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top