Add PivotTable filter Based on Range

B

breynolds0431

Active Member
Joined
Feb 15, 2013
Messages
303
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi. I have a combo box that the user selects their own name. The linked cell to the combo box is a named range. I was hoping to use that named range to apply a filter based on the name to four pivot tables. I was hoping this would do the trick, with the range set to the CurrentPage filter, but that was a no. :)

Code: 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DR - L1")        
        .Orientation = xlPageField
        .Position = 1
End With
    
ActiveSheet.PivotTables("PivotTable1").PivotFields("DR - L1"). _
        CurrentPage = Sheets("dd").Range("AudSelected")
        
  
    ActiveSheet.PivotTables("PivotTable1").PivotFields("DR - L1"). _
        EnableMultiplePageItems = True
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Try this.
The code does the following, activates all the items in the field, then searches for each item within the range name, if it does not exist then it hides, that way you will only see the items that are in the range name.

Note: at least one item must remain visible, if you want to hide all, the code sends you a notice.

Code: 
Sub test()
    Dim b As Range, wItem As PivotItem
    Dim n As Long, wDato As String
    
    Application.ScreenUpdating = False
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("DR - L1")
        .Orientation = xlPageField
        .Position = 1
        .EnableMultiplePageItems = True
    
        For Each wItem In .PivotItems
            .PivotItems(wItem.Value).Visible = True
        Next
        For Each wItem In .PivotItems
            Set b = Sheets("dd").Range("AudSelected").Find(wItem.Value, LookIn:=xlValues, lookat:=xlWhole)
            If b Is Nothing Then
                n = n + 1
                If n < .PivotItems.Count Then
                    .PivotItems(wItem.Value).Visible = False
                Else
                    MsgBox "No match"
                End If
            End If
        Next
    End With
  
End Sub
 
Upvote 0
That worked great! However, it stopped after PivotTable1. Is there away for it to go through and do the same for the other three PivotTables ? They are all on the same Active Sheet. I tried copying all from With to End With and updating the PivotTable numbering and PivotFields value, but it found its way to displaying the "No Match" message after only setting PivotTable1 to AudSelected.
 
Upvote 0
Missing n = 0

Code: 
Sub test()    Dim b As Range, wItem As PivotItem
    Dim n As Long, wDato As String
    Dim wTable As PivotTable
    Application.ScreenUpdating = False
    
    For Each wTable In ActiveSheet.PivotTables
        With wTable.PivotFields("DR - L1")
            .Orientation = xlPageField
            .Position = 1
            .EnableMultiplePageItems = True
        
            For Each wItem In .PivotItems
                .PivotItems(wItem.Value).Visible = True
            Next
[B][COLOR=#0000ff]            n = 0[/COLOR][/B]
            For Each wItem In .PivotItems
                Set b = Sheets("dd").Range("AudSelected").Find(wItem.Value, LookIn:=xlValues, lookat:=xlWhole)
                If b Is Nothing Then
                    n = n + 1
                    If n < .PivotItems.Count Then
                        .PivotItems(wItem.Value).Visible = False
                    Else
                        MsgBox "No match"
                    End If
                End If
            Next
        End With
    Next
End Sub
 
Upvote 0
Thanks again for looking into this. However, it still stops at PivotTable1. Would the issue be that each pivot has a different PivotTable field for the filter?


For example:

PivotTable2 has filter set for Field "DR - L2"
PivotTable3 has filter set for Field "Aud - L1"
PivotTable4 ihas filter set for Field "Aud - L2"


DanteAmor said:
Missing n = 0

Code: 
Sub test()    Dim b As Range, wItem As PivotItem
    Dim n As Long, wDato As String
    Dim wTable As PivotTable
    Application.ScreenUpdating = False
    
    For Each wTable In ActiveSheet.PivotTables
        With wTable.PivotFields("DR - L1")
            .Orientation = xlPageField
            .Position = 1
            .EnableMultiplePageItems = True
        
            For Each wItem In .PivotItems
                .PivotItems(wItem.Value).Visible = True
            Next
[B][COLOR=#0000ff]            n = 0[/COLOR][/B]
            For Each wItem In .PivotItems
                Set b = Sheets("dd").Range("AudSelected").Find(wItem.Value, LookIn:=xlValues, lookat:=xlWhole)
                If b Is Nothing Then
                    n = n + 1
                    If n < .PivotItems.Count Then
                        .PivotItems(wItem.Value).Visible = False
                    Else
                        MsgBox "No match"
                    End If
                End If
            Next
        End With
    Next
End Sub
Click to expand...
 
Upvote 0
update this:


Code: 
Sub test()
    Dim b As Range, wItem As PivotItem
    Dim n As Long, wDato As String
    
    Application.ScreenUpdating = False
    [COLOR=#0000ff]n = 0[/COLOR]
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("DR - L1")
        .Orientation = xlPageField
        .Position = 1
        .EnableMultiplePageItems = True
    
        For Each wItem In .PivotItems
            .PivotItems(wItem.Value).Visible = True
        Next
        For Each wItem In .PivotItems
            Set b = Sheets("dd").Range("AudSelected").Find(wItem.Value, LookIn:=xlValues, lookat:=xlWhole)
            If b Is Nothing Then
                n = n + 1
                If n < .PivotItems.Count Then
                    .PivotItems(wItem.Value).Visible = False
                Else
                    MsgBox "No match"
                End If
            End If
        Next
    End With
  
    [COLOR=#0000FF]n = 0[/COLOR]
    With ActiveSheet.PivotTables("PivotTable[COLOR=#ff0000]2[/COLOR]").PivotFields("[COLOR=#ff0000]DR - L2[/COLOR]")
        .Orientation = xlPageField
        .Position = 1
        .EnableMultiplePageItems = True
    
        For Each wItem In .PivotItems
            .PivotItems(wItem.Value).Visible = True
        Next
        For Each wItem In .PivotItems
            Set b = Sheets("dd").Range("AudSelected").Find(wItem.Value, LookIn:=xlValues, lookat:=xlWhole)
            If b Is Nothing Then
                n = n + 1
                If n < .PivotItems.Count Then
                    .PivotItems(wItem.Value).Visible = False
                Else
                    MsgBox "No match"
                End If
            End If
        Next
    End With

    [COLOR=#0000FF]n = 0[/COLOR]
    With ActiveSheet.PivotTables("PivotTable[COLOR=#ff0000]3[/COLOR]").PivotFields("[COLOR=#ff0000]Aud - L1[/COLOR]")
        .Orientation = xlPageField
        .Position = 1
        .EnableMultiplePageItems = True
    
        For Each wItem In .PivotItems
            .PivotItems(wItem.Value).Visible = True
        Next
        For Each wItem In .PivotItems
            Set b = Sheets("dd").Range("AudSelected").Find(wItem.Value, LookIn:=xlValues, lookat:=xlWhole)
            If b Is Nothing Then
                n = n + 1
                If n < .PivotItems.Count Then
                    .PivotItems(wItem.Value).Visible = False
                Else
                    MsgBox "No match"
                End If
            End If
        Next
    End With

End Sub
 
Last edited:
Upvote 0
Youre welcome and thanks for the feedback
 
Upvote 0
I found something

You can change this

Code: 
        For Each wItem In .PivotItems            
              .PivotItems(wItem.Value).Visible = True
        Next


By

Code: 
.[COLOR=#0000ff]ClearAllFilters[/COLOR]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA Filter Pivot Table Question
Replies
0
Views
162
gmazza76
G
T
  • Question
VBA Dynamic PivotTable Name
Replies
4
Views
537
th259
T
F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
237
Domenic
D
S
  • Solved
VBA for Pivot to filter on cell value not working
2
Replies
18
Views
926
Alex Blakenburg
A
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
661
Steve48
S

Forum statistics

Threads
1,215,596
Messages
6,125,726
Members
449,255
Latest member
whatdoido

Share this page

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
Back
Top