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!

 

[CsJHUN]

Hi Birch, i would do this:

Sub wsbirch_pivotfilter()
'show all pivot item
   With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotFilters
        Do While .Count > 0
            .Item(1).Delete
        Loop
    End With

'define Select Case Item
sci = ActiveSheet.Range("G1").Value & ActiveSheet.Range("G2").Value

Select Case sci
Case Is = "00"
Case Is = "01"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO")
        .PivotItems("87454").Visible = True
    End With
Case Is = "10"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO")
        .PivotItems("87456").Visible = True
    End With
Case Is = "11"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO")
        .PivotItems("87454").Visible = True
        .PivotItems("87456").Visible = True
    End With
End Select


End Sub

 

[WSBirch]

Thank you for your time!

I have entered this and the error I get is on the " .PivotItems("87454").Visible = True " line just under the Select Case sci, Case Is 00 and 01.

At the current, I know the value " 87454 " does exist, but " 87456 " does not, which based on your code I assume should produce "01" which is the section the code is having issue with.
Specifically, the debug error says, " Run-time error '1004': Unable to set the Visible property of the PivotItem class "

Does this help?

 

[WSBirch]

I'm sorry for the double-post.

I actually realized that the formula results were reversed in G1 and G2. 87456 exists, 87454 does not, but the formula results were reversed and then clashed with the VBA code because it was telling it to look for 87454 that didn't exist when 87456 did. So I fixed that mistake. Code doesn't produce any errors - but the issue now is that it doesn't actually change the filters in the Pivot Fields filter. Just to check, I selected several other random values then ran the code, no errors, but also none of the filtering changed. It maintained the random values I had selected. It DID include the search for the correct 87456 value and added it to the filter, but it just didn't remove every other value that existed.

 

[CsJHUN]

Oh,

1st step against this problem usually refresh the pivotcache:

Sub wsbirch_pivotfilter()
ActiveSheet.PivotTables("PivotTable1").RefreshTable '<-added this line
'show all pivot item
   With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotFilters
        Do While .Count > 0
'rest of the code is not changed
end sub

second best idea is changing how many items will restrain

Sub wsbirch_pivotfilter()
ActiveSheet.PivotTables("PivotTable1").PivotCache.MissingItemsLimit = xlMissingItemsNone '<-added this line
'show all pivot item
   With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotFilters
        Do While .Count > 0
'rest of the code is not changed
end sub

now saw your new post, i had no issue with that code snipets in my task, have to test with dummy files. Get back ASAP.

 

[WSBirch]

Thank you,
I've added the refresh line first. I didn't notice any changes. It still added the value 87456 to the filter, but didn't remove the other filters.
I removed the refresh line and then used your backup idea. Same results.

Still no actual errors, but it just doesn't seem to remove all the values except 87456.

 

[CsJHUN]

I had no problem at all the named item ("CUSNO") could be in row, column or filter fields in pivot
Could you please check if more items available at the drop down box at pivot filter.
Also recorded some macro, after compare with my original i suggest change to this:

Sub wsbirch_pivotfilter()
'let update the cache
ActiveSheet.PivotTables("PivotTable1").RefreshTable

'show all pivotfilter item
ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").CurrentPage = "(All)" '<- added this line
'remove all item
   With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotFilters
        Do While .Count > 0
            .Item(1).Delete
        Loop
    End With

'define Select Case Item
sci = ActiveSheet.Range("G1").Value & ActiveSheet.Range("G2").Value

Select Case sci
Case Is = "00"
Case Is = "01"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO")
.PivotItems("87454").Visible = True
End With
Case Is = "10"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO")
.PivotItems("87456").Visible = True
End With
Case Is = "11"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO")
.PivotItems("87454").Visible = True
.PivotItems("87456").Visible = True
End With
End Select

End Sub

 

[WSBirch]

I just picked the first 3 values listed in the filter. After running the code, it adds value 87456 to the selection, but it doesn't remove all other selections, in this case, the three I have selected. Still no errors in the code at all even using the new code you sent that included the .CurrentPage line you added.

I'm thinking I could remove all the other values except for 87456 manually. It'll leave me with only the 87456 selected, but when I run into an instance of not having 87456 and only have 87454 available and the code won't remove the selection for 87456 from the filter, I imagine I'd run into the issue there?

 

[WSBirch]

I have run some tests on that last line of my last post here about whether it would produce an error if 87456 went away and instead I only had 87454. I don't get any errors. It does a really good job of flipping back and forth between both values or including both when they both exist. I just fudged some values from the column to test that it worked.

I manually removed all other values except for including only 87456. Ran the VBA, nothing changed, which is fine - then went back and fudged the values in the column from the source data to include the other value, ran the VBA again, and it did a really good job of flipping back and forth between the two values. It seems like the VBA removes/adds only those two values back and forth but doesn't remove other values that aren't 87456 or 87454 - which should be totally fine for my purposes here. I shouldn't actually have any other values included on this table other than those two numbers...

I think it's safe to say we're resolved! If there's any issues for some reason that pop up, I'll be sure to post in here!

Thank you for all of your help with this!

 

[CsJHUN]

Hi,

changed my point of view, could you try this:
(maybe you have to add " around the 87...")

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"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO")
For Each PivotItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSNO").PivotItems
If PivotItem <> 87456  or pivotitem <> 87454 Then
PivotItem.Visible = False
End If
End Select

End Sub

i will check back later (for me next morning)
Good luck