VBA/macro to filter Pivot Table Filter Field

[alag76]

Hi everyone,

I'm trying to find a way to filter by number range in the pivot table filter field. It does not look like there is a way to do this in the pivot table filter field directly (i.e. filter >100). I have to click each value individually manually. The name of the filtered field is Days Open. Can someone help with a macros that could do this and select all the values >100 in the filter field? Thank you!

23640

[Paul_Hossler]

I generalized some macro recorder code with a small PT




Sub Macro1()
Dim oPI As PivotItem

With ActiveSheet.PivotTables(1).PivotFields("CCCC")
For Each oPI In .PivotItems
oPI.Visible = True
Next
For Each oPI In .PivotItems
oPI.Visible = (oPI.Value >= 30)
Next
End With
End Sub

[alag76]

Thank you for your help. I'll try this out!

[JBrownutah]

This helped me solve a similar challenge. I have multiple tables on each sheet. How would I modify the code to move to each table on the sheet in succession? Is there a way to define multiple tables? Here is the code I used.
Sub Macro4()
'
' Macro4 Macro
Dim PI As PivotItem


With ActiveSheet.PivotTables("PivotTable2").PivotFields("Ticker")
For Each PI In .PivotItems
PI.Visible = True
Next
For Each PI In .PivotItems

.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
Next
End With
End Sub

[JBrownutah]

This helped me solve a similar challenge. I have multiple tables on each sheet. How would I modify the code to move to each table on the sheet in succession? Is there a way to define multiple tables? Here is the code I used.
Sub Macro4()
'
' Macro4 Macro
Dim PI As PivotItem


With ActiveSheet.PivotTables("PivotTable2").PivotFields("Ticker")
For Each PI In .PivotItems
PI.Visible = True
Next
For Each PI In .PivotItems

.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
Next
End With
End Sub

[Paul_Hossler]

Not tested, but probably something like this



Option Explicit


Sub Macro4()
Dim oWS As Worksheet
Dim oPT As PivotTable
Dim oPI As PivotItem


For Each oWS In ThisWorkbook.Worksheets
For Each oPT In oWS.PivotTables

On Error Resume Next ' in case no pivotfield "Ticker" or no pivotitems "z..."
'first make all PI's visible
For Each oPI In oPT.PivotFields("Ticker")
oPI.Visible = True
Next

With oPT.PivotFields("Ticker")
.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
End With

On Error GoTo 0
Next
Next
End Sub

[JBrownutah]

Thanks Paul;

One thing I failed to mention is that this macro would be run on only certain sheets. I could explain why if you wish. But I would like to run this on only the active sheet. If you would like to see the sheet, I can provide that as well.

But to avoid making changes to all the the worksheets in the workbook, I commented out that reference and its corresponding "Next".

I got a Run-time error

"'91 Object variable or with block variable not set".

The debug highlighted the...


"For Each oPT In oWS.PivotTables"

...line of code.

I confess I am pretty new to VBA and still don't have a good feel for how this should work. That said I am very grateful for your kind assistance.

Jeff

[JBrownutah]

I tried replicating the code I posted last night for each of the 5 Pivot tables on the sheet (Capture15-Capture1). Pretty crude way of doing it, but it worked.

I would like to learn how to better use looping to do this sort of thing. In any event your suggestion got me going in the right direction.

Here is the code.
Sub Macro4()
'
' Macro4 Macro
Dim PI As PivotItem


With ActiveSheet.PivotTables("Capture15").PivotFields("Ticker")
For Each PI In .PivotItems
PI.Visible = True
Next
For Each PI In .PivotItems

.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
Next
End With

With ActiveSheet.PivotTables("Capture10").PivotFields("Ticker")
For Each PI In .PivotItems
PI.Visible = True
Next
For Each PI In .PivotItems

.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
Next
End With

With ActiveSheet.PivotTables("Capture5").PivotFields("Ticker")
For Each PI In .PivotItems
PI.Visible = True
Next
For Each PI In .PivotItems

.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
Next
End With

With ActiveSheet.PivotTables("Capture3").PivotFields("Ticker")
For Each PI In .PivotItems
PI.Visible = True
Next
For Each PI In .PivotItems

.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
Next
End With

With ActiveSheet.PivotTables("Capture1").PivotFields("Ticker")
For Each PI In .PivotItems
PI.Visible = True
Next
For Each PI In .PivotItems

.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
Next
End With


End Sub



How would you suggest I enclose this in either a For loop or a with loop and what are the advantages of each?

[Paul_Hossler]

I tried replicating the code I posted last night for each of the 5 Pivot tables on the sheet (Capture15-Capture1). Pretty crude way of doing it, but it worked.

I would like to learn how to better use looping to do this sort of thing. In any event your suggestion got me going in the right direction.

How would you suggest I enclose this in either a For loop or a with loop and what are the advantages of each?

If I understand the question, this will do all PTs on each of the 3 sheets on the Case line



Option Explicit

Sub Macro4()
Dim oWS As Worksheet
Dim oPT As PivotTable
Dim oPI As PivotItem

For Each oWS In ThisWorkbook.Worksheets
Select Case oWS.Name
Case "Sheet1", "Sheet3", "Sheet5" ' etc.
For Each oPT In oWS.PivotTables

On Error Resume Next ' in case no pivotfield "Ticker" or no pivotitems "z..."
'first make all PI's visible
For Each oPI In oPT.PivotFields("Ticker")
oPI.Visible = True
Next

With oPT.PivotFields("Ticker")
.PivotItems("z20").Visible = False
.PivotItems("z40").Visible = False
.PivotItems("z60").Visible = False
.PivotItems("z80").Visible = False
.PivotItems("zAvg").Visible = False
.PivotItems("zMed").Visible = False
End With

On Error GoTo 0
Next
End Select
Next
End Sub

[alag76]

For CCCC I should replace with the name of the field right? (i.e. Days Open) Thank you!


I generalized some macro recorder code with a small PT




Sub Macro1()
Dim oPI As PivotItem

With ActiveSheet.PivotTables(1).PivotFields("CCCC")
For Each oPI In .PivotItems
oPI.Visible = True
Next
For Each oPI In .PivotItems
oPI.Visible = (oPI.Value >= 30)
Next
End With
End Sub

[Paul_Hossler]

Yes

Let me know how it goes

[JBrownutah]

Thanks Paul;

This worked for every spreadsheet but the first one. Case "Drawdown Sev".

I have attached the workbook.

I renamed the code you provided as Macro5, preserving Macro4.

In some of the sheets I have two pivot tables, one in which I do want to display the z... Tickers , and one that I don't want them to display since they have no meaning in the scatter plot. I think for these it would be best to have individual macros (copies of the same code executed with a button so as not to disturb the other pivot tables.

The general code you suggested is helpful to do some of the cleanup of the workbook.

Best,

Jeff

23674

[Paul_Hossler]

My bad -- I forgot the collection on the line below, and the On Error Resume Next ignored my mistake




For Each oPI In oPT.PivotFields("Ticker").PivotItems

[JBrownutah]

Hey Paul;
23675
Thanks. That worked for the first sheet, but one that has multiple PTs (capture) it got only the first PT and left the other four alone.

[Paul_Hossler]

Problem was the trailing space after the tab name "Capture "

Fixed that, and it worked as expected

[Robandemmy]

I'm looking for a similar VBA that will filter based on a range. If cells H1:H4 contain the values 5, 15, 25, 80, then filter those values. If the range changes to H1:H7 with values 4,23,54,67,71,80,84 then filter on those instead. Is this possible?

[paulked]

@ Robandemmy

Welcome to the forum, but please start a new thread with this question. A hijack of a hijacked thread is never good news to an old confused guy like me!