Slicer disconnects from pivottable when programatically filtering items in pivot fields

[Telefonstolpe]

Hi,

I have a pivot table (with a pivot chart) and several slicers connected to the pivot table.
Some of the "filter fields" contain more than 10000 items.
The slicers work very well in the sense that if you filter in the slicers it will also filter quickly in the pivot table (and the chart) and vice versa.

However, when I run a macro that filters pivotfields I risk getting into trouble...
In pivot tables it seems we need to filter one item at a time. With fields containg more than 10000 items it would take too much time to filter these fields.
Therefore i've built the macro the following way...

The macro loops through all page fields ("filter fields") in the pivot table and choose one of five options in each field:
1. If the field should have no filters: Clear all filters
2. If the field should has only one filter: Choose EnableMultiplePageItems = False and then filter the chosen Item
3. If the macro must filter more than X number of items, stop the macro since the procedure will take to much time
4. If the macro must filter more than half of the items in the field: Clear all filters and make invisible those items that are NOT to be filtered
5. If the macro must filter less than half of the items in the field:
Clear all filters > EnableMultiplePageItems = False > Filter first item > EnableMultiplePageItems = True > filter first item with the slicer > filter the rest of the items
(This is the only solution ive come up with for option number five)

It is the fifth alternative that is the problem. So what happens is that the slicer gets disconnected from the pivot table when I use the slicer to filter the field in the macro
If you try to filter in the slicer nothing happens in the pivot table. The only way I found to reconnect the slicer to the pivot table by manually going to the table or chart and filter the field there.
But that is not a good solution

So my question is:
Is there a way to programatically connect the slicer to the pivot table again?
Or is there any other way to code option 5?

Se the relevant parts of the code below

Sub AnnoyingMacro()
Dim PfName As String
Dim pi As PivotItem
Dim Rng As Range
Dim Cel As Range
Dim FirstItem As String
Dim AddItems As Boolean
Dim NumberOfItems As Long
Dim SliName As String

'A lot of code irrelevant for this question...
    
    'Filter each pivotfield
    For Each pf In pt.PivotFields
    Pfnamn = pf.Name
    If pf.Orientation = xlPageField Then 'Only apply the macro to "filter fields"
    
        'A lot of code irrelevant for this question...
        
        'If We're gonna add one items to the field
        'irrelevant code for this question...
        
        'If We're gonna add one items to the field
        'irrelevant code for this question...
        
        'If We're gonna remove one or more items from the field
        'irrelevant code for this question...
    
        'If We're gonna add multiple items to the field
        If AddItems = True Then
        If NumberOfItems > 1 Then
                
            'Filter ONE item with help of Slicer
            'This is the only way ive found where you can filter just ONE item directly in a multifilter setting
            pt.PivotFields(PfName).ClearAllFilters
            pt.PivotFields(PfName).EnableMultiplePageItems = False
            pt.PivotFields(PfName).CurrentPage = FirstItem
            pt.PivotFields(PfName).EnableMultiplePageItems = True
            SliName = "x_" & PfName
            ActiveWorkbook.SlicerCaches(SliName).SlicerItems(FirstItem).Selected = True
            
            'Filter the REST directly in the pivot table field
            'Rng = range in the workbook that contain all items that we want to filter in all the pivot fields where FirstItem = the first cell in the range
            pt.PivotCache.Refresh
            For Each Cel In Rng
                 If Cel.Offset(0, -1).Value = PfName Then pt.PivotFields(PfName).PivotItems(Cel.Value).Visible = True
            Next Cel
            
            'First Failed attemt resolve the connection problem...
            'Remove the filter that was made in the slicer and make the filter in the pivot field instead
            pt.PivotCache.Refresh
            ActiveWorkbook.SlicerCaches(SliName).SlicerItems(FirstItem).Selected = False
            pt.PivotFields(PfName).PivotItems(FirstItem).Visible = True
            
            'Second Failed attemt resolve the connection problem...
            'Run a submacro that deletes the slicer and creates a new slicer that hopefully is correctly connected to the pivot table
            pt.PivotCache.Refresh
            Call RegenerateSlicer

            'Third Failed attemt
            pt.PivotCache.Refresh
                        
        End If
        End If
    
    End If
    Next pf

'A lot of more code irrelevant for this question...

End Sub

 

[MarkKBrennan]

Line 15 in your code has a typo. "Pfnamn = pf.Name".
Could this have an affect on your problem or is the error only in this snippet and not the original?

Hope this helps

 

[Telefonstolpe]

Hi Mark and thank you for your answer!

Unfortunately the typo has nothing to do with the issue at hand, and is not in the actual code.

I never managed to solve the problem, but instead had to make a workaround that works as follows:

If the filtering is not enabled because it is done using a slicer, the slicer will be hidden and a message will appear telling the user that he must select the field(s) in the pivot chart that has/have not been properly filtered and click "OK" . When you do that, the filter on the specific field will be automatically updated correctly, and through a calculation event that I have entered, the slicer connected to the specific field will become visible again. It is of course not the best solution but it works...