vba code to filter a pivot table based on a cell vlaue being <=

anaiscarrasco

New Member
Joined
Aug 12, 2019
Messages
1
Hi, I need help to filter my pivot table by cell value being less or equal to. this is what I have

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("I12:J12")) Is Nothing Then Exit Sub


Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("Swap_Table_EU").PivotTables("PivotTable2")
Set Field = pt.PivotFields("ASP + 20")
NewCat = Worksheets("Swap_Table_EU").Range("<=J12").Value

Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable

End With

End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I don't know how your data is in the table.


But I guess the "ASP + 20" field is in the "Report filter" area.
I also assume that I2:J2 is a combined cell, in that case we must take I2.
I also assume that you want to perform the filter when you modify cell I2, in this case the event of the sheet must be Change.


Code:
Private Sub Worksheet_[B][COLOR=#0000ff]Change[/COLOR][/B](ByVal Target As Range)
  If Intersect(Target, Range("I12:J12")) Is Nothing Then Exit Sub
  If Target.Count > 2 Then Exit Sub
    Dim pi As PivotItem
    Dim n As Long
    With Worksheets("Swap_Table_EU").PivotTables("PivotTable2").PivotFields("ASP + 20")
        .ClearAllFilters
        For Each pi In .PivotItems
            If Val(pi) > Range("I12").Value Then
                n = n + 1
                If n < .PivotItems.Count Then
                    pi.Visible = False
                Else
                    MsgBox "No match"
                    .ClearAllFilters
                End If
            End If
        Next
    End With
End Sub



If it is not what you need, then you must explain how your data is on the pivot table and on the sheet.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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