Excel 2007, Pivot Tables, Why are filters available when fields are in Axis Fields, but not when they are in the Report Filter?

[VBAProIWish]

Excel 2007, Pivot Tables, Why are filters available when fields are in Axis Fields, but not when they are in the Report Filter?


Just wondering this answer. It took me a long time to figure out that the Label and Value filters that ARE available in the Axis Field on a Pivot Table report, but as soon as you move that field to the report filter, the option to filter is no longer there.

Is there a reason for this?

Thanks

 

[marcelo667]

I´m having the same problem. I need my macro to filter a label but, as the field I need to filter is a row filter, I can´t.
Does anybody else had this problem already and found out a way to solve?

I searched the hole forum but this was the only topic related to my problem that I could find.

Right now I´m using the following macro, but when there are lot of pivotItems the macro take very long to run:

code:

Sub FILTRA_VALOR(nomeWorkSheetTD, NomeTD, nomeTDFiltro, valorDesejadoFiltrar)
Dim pviItem As PivotItem
Dim pvfField As PivotField
Application.ScreenUpdating = False
Set pvfField = Sheets(nomeWorkSheetTD).PivotTables(NomeTD) _
.PivotFields(nomeTDFiltro)
With Sheets(nomeWorkSheetTD).PivotTables(NomeTD).PivotFields(nomeTDFiltro)
For Each pviItem In pvfField.PivotItems
If pviItem <> valorDesejadoFiltrar Then
a = pviItem
.PivotItems(a).Visible = False
End If
Next
End With
Application.ScreenUpdating = True
End Sub

 

[marcelo667]

A friend of mine helped me with this problem so I´ll post the solution here in case someone else has the same doubt.

Just need to use this code:

Sheets(WorkSheetPT).PivotTables(PT).PivotFields(TDFilter).CurrentPage = label

It will filter de Row Filter with the label you need.

 

[VBAProIWish]

Thanks for posting the solution

 

[wlyqyq]

Would you please elaborate on how to make this happen? I add a form button, then assign the following macro on the button click?

A friend of mine helped me with this problem so I´ll post the solution here in case someone else has the same doubt.

Just need to use this code:

Sheets(WorkSheetPT).PivotTables(PT).PivotFields(TDFilter).CurrentPage = label

It will filter de Row Filter with the label you need.

 

[wlyqyq]

How do you use this Macro? Can you elaborate please?

A friend of mine helped me with this problem so I´ll post the solution here in case someone else has the same doubt.

Just need to use this code:

Sheets(WorkSheetPT).PivotTables(PT).PivotFields(TDFilter).CurrentPage = label

It will filter de Row Filter with the label you need.