VBA, Unselect all PivotField filters then select 2 values

[WSBirch]

Good morning,

I'm pretty unfamiliar with coding any VBA at all, but I do well enough reading it.

I'm trying to unselect all filtered values from PivotTable "PivotTable1" PivotFields "CUSNO", then select 2 values that would be in the list of values based on if they exist or not. There's generally anywhere from 40-200 different values listed in the filter that change dynamically day to day. Also, both values I want to have selected sometimes both show up or often times one or the other and not both.



I have a column on a separate worksheet that is refreshed and updated that represents every value that currently exists in the "CUSNO" PivotField. The source data for "CUSNO" PivotField.

On the same worksheet as the Pivot Table, I have the below two cells that check on if that column contains either value I'm looking for.
In cell G1, I check if value "87456" exists in that column. If it does, G1 = 1, if it does not then G1 = 0.
In cell G2, I check if value "87454" exists in that column. If it does, G2 = 1, if it does not then G2 = 0.

I'm hoping to gather VBA code that does this:
IF(AND(G1=1,G2=1), Run VBA that unselects all CUSNO PivotField values and then selects only values "87456" and "87454"
IF(AND(G1=1,G2=0), Run VBA that unselects all CUSNO PivotField values and then selects only value "87456"
IF(AND(G1=0,G2=1), Run VBA that unselects all CUSNO PivotField values and then selects only value "87454"
IF(AND(G1=0,G2=0), well, do nothing then.



Thank you for any possible help on this. It's greatly appreciated!

 

[WSBirch]

I'm trying the new code on another test file, but I get a Compile Error on this one on the last line "End Select".

" Compile Error: End Select without Select Case "

 

[CsJHUN]

sorry i was in hurry going home.
the corrected code:

Sub wsbirch_pivotfilter()
    ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").ClearAllFilters
    
    'define Select Case Item
    sci = ActiveSheet.Range("G1").Value & ActiveSheet.Range("G2").Value
    
    Select Case sci
        Case Is = "00"
        Case Is = "01"
            For Each PivotItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotItems
                If PivotItem <> 87454 Then
                    PivotItem.Visible = FALSE
                End If
            Next
        Case Is = "10"
            For Each PivotItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotItems
                If PivotItem <> 87456 Then
                    PivotItem.Visible = FALSE
                End If
            Next
        Case Is = "11"
            For Each PivotItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotItems
                If PivotItem <> 87456  Or pivotitem <> 87454 Then
                    PivotItem.Visible = FALSE
                End If
            Next
    End Select
    
End Sub

 

[WSBirch]

Not a problem, I totally understand.

That worked perfect!!
Thank you!