Excel VBA re-apply pivot table data source

Question

I have a macro that copies two sheets from my workbook to their own workbook. One sheet has some data defined with a named range, the second has multiple pivot tables, all with the data source as said named range candData.

Once the sheets have copied my slicers lose some of their Report Connections.

In order to reconnect them, I have to manually select each pivot table and set the data source again, even though the data source is already set. (I simply click Change Data Source and immediately click OK, without actually changing anything.)

Once I have done this for each pivot table all report connections are showing again.

I have the following macro to loop through each pivot table and re apply the data source, then reconnect each slicer connection, however even after the data source has been applied, I still have to manually select each pivot table in order to reconnect the slicers.

I am getting no errors, it steps through each pivot table as expected and resets the data source. Any clues why the report connections only work when I set the data source manually instead of through the following?

Sub setSlicerSource()

Dim MyPivot As PivotTable
Dim slCaches As SlicerCaches
Dim slCache As SlicerCache

Set slCaches = ActiveWorkbook.SlicerCaches

With ActiveWorkbook
    For Each MyPivot In .Sheets("Pivots").PivotTables
        MyPivot.ChangePivotCache .PivotCaches.Create(SourceType:=xlDatabase, SourceData:="candData")
    Next MyPivot

    For Each slCache In slCaches
        For Each MyPivot In .Sheets("Pivots").PivotTables
            slCache.PivotTables.AddPivotTable MyPivot
        Next MyPivot
    Next slCache
End With
End Sub

I haven't used slicers much but... what about doing another PivotTable.RefreshTable after you update the slicers (even though changing the pivot cache would logically seem to do this as well). Also, this seems like it may be useful if you haven't seen it. — PeterT
Thank you for that link. I have tried a table refresh after updating each data source, every which way of working with the pivot tables through VBA does nothing annoyingly. It will only do a 'hard' refresh when I set the data source manually seemingly. — Tim Wilkinson
When manually setting the data source for a pivottable, Excel will automatically reuse an existing pivotcache that covers the same range by default. Your code creates a new pivotcache for each pivottable which will firstly blow out the size of your file, but may also cause an issue with slicers across multiple caches. Have you tried creating just one cache first, then setting all pivottables to use that cache? — Michael
I haven't tried that yet, mostly because I don't think I understand, surely when this sheet gets copied in the first place there is already a pivotcache created? I would presume a table refresh would update this pivotcache but that doesn't appear to happen. Is there a way to delete a pivotcache and create a new one? — Tim Wilkinson

Michael Michael · Accepted Answer · 2019-08-29T12:31:39

I'm not sure if this will actually solve your problem, but it's the first thing I'd try fixing regardless:

This line currently creates a new PivotCache for each PivotTable:

MyPivot.ChangePivotCache .PivotCaches.Create(SourceType:=xlDatabase, SourceData:="candData")

Instead, create a PivotCache for PivotTables(1) first before the loop:

.Sheets("Pivots").PivotTables(1).ChangePivotCache .PivotCaches.Create(SourceType:=xlDatabase, SourceData:="candData")

Then in the loop, set all PivotTables to use the cache of PivotTables(1)

MyPivot.ChangePivotCache .Sheets("Pivots").PivotTables(1)

Excel VBA re-apply pivot table data source

1 Answers