Issue Setting Pivot Table Filter Using Excel VBA

0
votes

Community,

I am trying to "default" some filtering so that users save time / reduce errors for some recurring reports, but stuck on setting the Pivot Field "RT...1004 : "Unable to get the PivotFields property of the PivotTable Class".

The field does exist, with that name (tried other ones too). The PT itself refers to a data table in excel.

Looking at examples around the web I really don't see an error in below code, but obviously there is. The code does work if I comment out the "Set pf01" and pf01.CurrentPage, the filters are then all cleared so the references to pivottable are correct..

Sub TestSetpvt()

Dim wks As Worksheet
Dim pvt As PivotTable
Dim pf01 As PivotField

Set wks = ActiveSheet
Set pvt = wks.PivotTables("RPT_OTH")
Set pf01 = pvt.PivotFields("Q_INCL")

pvt.ClearAllFilters

pf01.CurrentPage = "Y"
2
excelvba
this explanation is not very clear. Can you add more information please. – MEdwin
Not sure what you really want, but have you considered conditional formatting? cells that only accept numbers within a range or integer or accept text or particular words... Data validation is also a possibility... – Solar Mike
The issue is that the command 'Set pf01 = pvt.PivotFields("Q_INCL") ' throws the error RT...1004 : "Unable to get the PivotFields property of the PivotTable Class". As if the field doesn't exist .. – user10539437
The code you posted works without issue for me. I tried that with a dummy data table. Can you send a screenshot of your pivot table, maybe just the upper left corner of it? – SNicolaou
Also, i believe that your field "Q_INCL" has to be a filter field. If it is a row field i think that you might not be able to achieve the filtering in this way. – SNicolaou

2 Answers

0
votes

I tested a couple of things and was able to reproduce your issue. You need to have your field "Q_INCL" as a filter field for your filtering to work with the code you provided.

if it is a row field, you will get the error you posted. enter image description here

0
votes

This works.., looks like this part of excel / excel vba is more complex then I'd like it to be :-/

Dim wks As Worksheet
Dim pvt As PivotTable
Dim pf01 As PivotField

Set wks = ActiveSheet
Set pvt = wks.PivotTables("RPT_OTH")
Set pf01 = pvt.PivotFields("[TBL_TCB].[Q_INCL].[Q_INCL]")

pf01.VisibleItemsList = Array("[TBL_TCB].[Q_INCL].&[Y]")