vba code to filter a pivot table based on a cell vlaue being <=
Results 1 to 2 of 2

Thread: vba code to filter a pivot table based on a cell vlaue being <=
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

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

    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_Change(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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •