Slicer connections does not show pivottable post c

Question

I have two pivottables in two different sheets (Sheet1 and Sheet2) sourcing data from a common sheet (Sheet3). A slicer is created on the pivottable on Sheet1. If we go to report connections, we can see both pivottables in the list.

Now I am dynamically changing the pivot data source for both the pivottables one by one. Only change is the range is extended to include the new rows copied by a different process. Once the code is executed, the report connections does not show both the pivottables anymore. It just shows one.

using the below code to change pivot data source.

                    Dim objwrksheet As Worksheet = mWorkBook.Worksheets(mPivotWorksheetname)
                    Dim objwrksheet2 As Worksheet = mWorkBook.Worksheets(mDataWorksheetname)
                    If Not IsNothing(objwrksheet) Then
                        Dim objpivottable As PivotTable = objwrksheet.PivotTables(mPivotTable)
                        If objpivottable IsNot Nothing Then
                            Dim sourceDataRange As Range = objwrksheet2.Range(mSourceRange)
                            Dim cache As PivotCache = mWorkBook.PivotCaches.Create(SourceType:=XlPivotTableSourceType.xlDatabase, SourceData:=sourceDataRange)
                            objpivottable.ChangePivotCache(cache)
                            objpivottable.RefreshTable()
                            mRetval = "Successful"
                        Else
                            mRetval = "Pivot open failed"
                        End If
                    Else
                        mRetval = "Worksheet open failed"
                    End If

Expected result should be that after the change data source for both the pivot tables, the slicer report connections should continue to show both the pivottable names in the list.

Answer 1

This is a general VBA approach:

You can change the PivotTable.SourceData in general by adding a new PivotCache to the workbook. But if this pivottable is included in a slicer, you have to uncheck its report connection first by SlicerCache.PivotTables.RemovePivotTable().

If you change several pivottables' sourcedata, you can only reassign their report connections within the slicer again, if all included pivottables are based on the same PivotCache.

Therefore after changing the first pivottable's sourcedata, you have to "reuse" its new pivotcache for all other pivottables. This "reuse" can be done by setting the PivotTable.CacheIndex, which works as long as the further pivottables use the same pivotfields (or subset of them) like the first pivottable.

Remark: To use following code, all report connections of the slicer need to be enabled first (as SlicerCache.PivotTables returns the checked report connections only).

Private Sub ChangeAllPivotSources()
    Dim objSlicerCache As SlicerCache
    Dim objPivotTable As PivotTable
    Dim objPivotTables() As PivotTable
    Dim i As Long

    ' get the slicercache, e. g. via its first pivottable:
    Set objPivotTable = ActiveWorkbook.Sheets(1).PivotTables(1)
    Set objSlicerCache = objPivotTable.Slicers(1).SlicerCache

    ' dimension array with all pivottable objects of the slicercache
    ReDim objPivotTables(1 To objSlicerCache.PivotTables.Count)

    ' remove all pivottables from slicer's report connections
    For i = objSlicerCache.PivotTables.Count To 1 Step -1
        Set objPivotTables(i) = objSlicerCache.PivotTables(i)
        objSlicerCache.PivotTables.RemovePivotTable objPivotTables(i)
    Next i

    ' create new pivotcache based on a new range for the first pivottable,
    ' use this pivotcache for all other pivottables also
    For i = 1 To UBound(objPivotTables)
        If i = 1 Then
            objPivotTables(i).ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
                SourceType:=xlDatabase, _
                SourceData:=ActiveWorkbook.Sheets(3).Range("A1").CurrentRegion)
        Else
            objPivotTables(i).CacheIndex = objPivotTables(1).PivotCache.Index
        End If
    Next i

    ' reassign the report connections again
    For i = 1 To UBound(objPivotTables)
        objSlicerCache.PivotTables.AddPivotTable objPivotTables(i)
    Next i

End Sub