none
Filter Pivot Table with a VBA script based on a list in a table on the same worksheet RRS feed

  • Question

  • Hello,

    I need assistance in creating a vba script to filter a pivot table, based off a list of values in a table within the same worksheet.

    In the table there's approximately 50 values I need to filter, and I require a vba script to avoid having to select each on individually.

    Worksheet Tab Name = "Product_Sales_By_Debtor"

    Pivot Table Name = "SalesReport_Pivot"

    Pivot Field to Filter = "STOCKCODE"

    Table Name of values to Filter = "STOCKCODES"

    Many thanks

    Friday, February 1, 2019 1:43 AM

Answers

  • The code produces an error because the StockCodes in your Pivot Table Source data are both numeric values and text where the StockCodes in your criteria table are numeric values only so they don't math and if no StockCode matches with the StockCode in the criteria table the code will produce an error because you cannot hide all the items from a Pivot Table RowField.

    Try something like this...

    Sub FilterPivotTable()
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim Codes
    
    Application.ScreenUpdating = False
    
    Set wsPivot = Worksheets("Product_Sales_By_Debtor")
    Set pt = wsPivot.PivotTables("SalesReport_Pivot")
    Set pf = pt.PivotFields("STOCKCODE")
    
    Codes = wsPivot.Range("STOCKCODE").Value
    
    pf.ClearAllFilters
    On Error Resume Next
    For Each pi In pf.PivotItems
        If IsNumeric(pi) Then
            If IsError(Application.Match(pi + 0, Codes, 0)) Then
                pi.Visible = False
            End If
        Else
            If IsError(Application.Match(pi, Codes, 0)) Then
                pi.Visible = False
            End If
        End If
    Next pi
    
    Application.ScreenUpdating = True
    End Sub



    Subodh Tiwari (Neeraj) sktneer


    Friday, February 1, 2019 10:20 AM

All replies

  • Please give this a try...

    Sub FilterPivotTable()
    Dim wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim Codes
    
    Application.ScreenUpdating = False
    
    Set wsPivot = Worksheets("Product_Sales_By_Debtor")
    Set pt = wsPivot.PivotTables("SalesReport_Pivot")
    Set pf = pt.PivotFields("STOCKCODE")
    
    Set tbl = wsPivot.ListObjects("STOCKCODES")
    
    Codes = tbl.ListColumns("STOCKCODE").DataBodyRange.Value
    
    pf.ClearAllFilters
    For Each pi In pf.PivotItems
        If IsError(Application.Match(pi, Codes, 0)) Then
            pi.Visible = False
        End If
    Next pi
    
    Application.ScreenUpdating = True
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    Friday, February 1, 2019 2:52 AM
  • When running the code it runs for a while and then:

    Run-time error '1004'; 

    Unable to set the Visible property of the PivotItem class

    • Edited by mctech2017 Friday, February 1, 2019 4:19 AM
    Friday, February 1, 2019 4:00 AM
  • In the question's description you said this...

    "Table Name of values to Filter = "STOCKCODES" "

    I assumed that you have an Excel Table named STOCKCODES on "Product_Sales_By_Debtor" Sheet.

    If you meant you have a named range called "STOCKCODES" on "Product_Sales_By_Debtor" Sheet, try this instead...

    Sub FilterPivotTable()
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim Codes
    
    Application.ScreenUpdating = False
    
    Set wsPivot = Worksheets("Product_Sales_By_Debtor")
    Set pt = wsPivot.PivotTables("SalesReport_Pivot")
    Set pf = pt.PivotFields("STOCKCODE")
    
    Codes = wsPivot.Range("STOCKCODE").Value
    
    pf.ClearAllFilters
    For Each pi In pf.PivotItems
        If IsError(Application.Match(pi, Codes, 0)) Then
            pi.Visible = False
        End If
    Next pi
    
    Application.ScreenUpdating = True
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    Friday, February 1, 2019 5:03 AM
  • still receiving this issue:

    When running the code it runs for a while and then:

    Run-time error '1004'; 

    Unable to set the Visible property of the PivotItem class

    Friday, February 1, 2019 5:10 AM
  • It's hard to debug without a file.

    If possible, upload your file to either Onedrive or Google Drive or DropBox and share the link of the file.


    Subodh Tiwari (Neeraj) sktneer

    Friday, February 1, 2019 7:14 AM
  • here's the link to the file

    https://www.dropbox.com/s/5gexkaq0151gjtg/Pivot_filter_example.xlsm?dl=0

    Friday, February 1, 2019 7:55 AM
  • The code produces an error because the StockCodes in your Pivot Table Source data are both numeric values and text where the StockCodes in your criteria table are numeric values only so they don't math and if no StockCode matches with the StockCode in the criteria table the code will produce an error because you cannot hide all the items from a Pivot Table RowField.

    Try something like this...

    Sub FilterPivotTable()
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim Codes
    
    Application.ScreenUpdating = False
    
    Set wsPivot = Worksheets("Product_Sales_By_Debtor")
    Set pt = wsPivot.PivotTables("SalesReport_Pivot")
    Set pf = pt.PivotFields("STOCKCODE")
    
    Codes = wsPivot.Range("STOCKCODE").Value
    
    pf.ClearAllFilters
    On Error Resume Next
    For Each pi In pf.PivotItems
        If IsNumeric(pi) Then
            If IsError(Application.Match(pi + 0, Codes, 0)) Then
                pi.Visible = False
            End If
        Else
            If IsError(Application.Match(pi, Codes, 0)) Then
                pi.Visible = False
            End If
        End If
    Next pi
    
    Application.ScreenUpdating = True
    End Sub



    Subodh Tiwari (Neeraj) sktneer


    Friday, February 1, 2019 10:20 AM