Filter Pivot table based upon list using VBA

[jakeman]

I have a pivot table that I would like to try to filter based upon values from a list on another worksheet, using VBA.

the pivot table is on a worksheet called Sheets("temp_UtilPivot") and the name of the pivot table is temp_UtilData_pivot.

The field in the pivot table is called PCP_Name and the list that I want the PCP_Name field to be filtered off of is on the sheet called Sheets("Lists") and the range of values is stored as a named range = PCP_Selections. PCP_Selections is a dynamic named range that changes in size based upon values added.

I'm using Excel 2010.

thanks.

 

[Andrew Poulsom]

What have you tried?

 

[jakeman]

Hi Andrew - honestly, I've scoured the internet for something and haven't found any code yet...then I tried to put my own piece of code that I put together but my workbook crashed and I lost what I put together before...frustrating.

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim PI As PivotItem
    With Worksheets("temp_UtilPivot").PivotTables("temp_UtilData_pivot").PivotFields("PCP_Name")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("PCP_Selections"), PI.Name) > 0
        Next PI
    End With
End Sub

 

[jakeman]

Mr Poulsom - you are a certified VBA Rockstar! Yes, it worked perfectly. You saved me an unbelievable amount of time with this code.

Thank you very much, sir!

 

[cuchulainem]

Hi, Andrew, I see your picture on the boards a lot and always know that the answers are good. This is awesome though! I've been trying to figure this out for days now. I've tried complex codes, and stacked For Loops, and Selcting Cases and Ifs. Thank you so much for helping all of us with codes! Happy holidays!

Eric

Does this work for you?

Sub Test()
    Dim PI As PivotItem
    With Worksheets("temp_UtilPivot").PivotTables("temp_UtilData_pivot").PivotFields("PCP_Name")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("PCP_Selections"), PI.Name) > 0
        Next PI
    End With
End Sub

 

[richardkwon4]

I know this is an old post but when I try to run the code I am getting an "Object Required" error. any idea why? Thanks!

 

[EX5TASY]

I know this is an old thread - but I am 6 different pivot tables and am therefore recreating this code 6 times. Everything works, but it takes about a minute for all of the data to populate. Being a complete VBA novice, is there something about the way this is coded (and if I duplicate it several times) that is redundant or taking up too much time?

 

[Andrew Poulsom]

Speed will depend on the number of Pivot Items in the Pivot Field. Have you set Calculation to manual?

 

[EX5TASY]

Speed will depend on the number of Pivot Items in the Pivot Field. Have you set Calculation to manual?

Unfortunately this does not apply to me - I have a dashboard which draws data from the pivot tables.

Here's my code after replicating 6x; is there anything that looks off?

Sub WTD()    Dim PI As PivotItem
    With Worksheets("Pivot").PivotTables("WTDPlan").PivotFields("FY Week")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("WTDTest"), PI.Name) > 0
        Next PI
        End With
        
    With Worksheets("Pivot").PivotTables("WTDActual").PivotFields("FY Week")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("WTDTest"), PI.Name) > 0
        Next PI
       
        End With


    With Worksheets("Pivot").PivotTables("MTDPlan").PivotFields("FY Week")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("MTDTest"), PI.Name) > 0
        Next PI
        End With
        
    With Worksheets("Pivot").PivotTables("MTDActual").PivotFields("FY Week")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("MTDTest"), PI.Name) > 0
        Next PI
        End With
    
    With Worksheets("Pivot").PivotTables("YTDPlan").PivotFields("FY Week")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("YTDTest"), PI.Name) > 0
        Next PI
        End With
        
    With Worksheets("Pivot").PivotTables("YTDActual").PivotFields("FY Week")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("YTDTest"), PI.Name) > 0
        Next PI
        End With
    
End Sub