CODEWITHSUNDEEP
excelexcel-2010pivot-tablevba
user2244385
user2244385

Reputation: 41

Connect a slicer to ll pivot table

Status Quo:
I have an excel 2010 workbook with a worksheet named Data. All the pivot tables in the workbook draw from that sheet. I have another sheet named Board where all the slicers are, and every slicer is connected to all pivot tables in the workbook.

Need:
I have to overhaul the file quite often, adding some columns in Data and some more pivots and slicers. Of course, pivot cache won't update automatically. Therefor new pivots can't be associated to old slicers.

Strategy:
1_ I'd like to get a macro to detach all slicers from all pivot tables. This way if I add a new pivot I don't need to go through every slicer once again to link it.
2_ then I'd like to set all pivot caches to what I decide (Range("A1").CurrentRegion on Data seems pretty cool, otherwise I could reserve a cell on Board that I update manually).
3_ third and last, attach every slicer to every pivot table in the workbook.

Achievements:
1_ did it for 1 slicer, guess a loop will do the trick
2_ kinda did it, but...meh
3_ no way. I can't get this done.

Any suggestions?
Thank you for your help, this would be really a time saver!!

Upvotes: 1

Views: 7243

Answers (1)

user2244385
user2244385

Reputation: 41

apparently I did it!!
I took some code from the internet, I forgot where. Hope this is useful to someone!!!

Sub ManageSlicers(Connect_Disconnect As String)
'feed in *connect* or *disconnect* accordingly to get it applied to all slicers in *Board*.
Dim oSlicer As Slicer
Dim oSlicercache As SlicerCache
'
Dim wks As Worksheet
Dim pt As PivotTable

For Each oSlicercache In ActiveWorkbook.SlicerCaches
    For Each oSlicer In oSlicercache.Slicers
        If oSlicer.Shape.BottomRightCell.Worksheet.Name = "Board" Then
            For Each wks In Worksheets
                For Each pt In wks.PivotTables
                    If Connect_Disconnect = "connect" Then
                        oSlicer.SlicerCache.PivotTables.AddPivotTable (Sheets(wks.Name).PivotTables(pt.Name))
                    ElseIf Connect_Disconnect = "disconnect" Then
                        oSlicer.SlicerCache.PivotTables.RemovePivotTable (Sheets(wks.Name).PivotTables(pt.Name))
                    Else
                        MsgBox "Macro ManageSlicers messed up."
                    End If
                Next
            Next
        End If
    Next
Next

Set oSlicer = Nothing
Set oSlicercache = Nothing
Set pt = Nothing
Set wks = Nothing
End Sub

Sub UpdatePivotCache()
'update pivottables cache
Dim wks As Worksheet
Dim pt As PivotTable

For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
        If lIndex = 0 Then
            pt.ChangePivotCache _
                ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                SourceData:=Sheets("Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
            Set ptMain = pt
            lIndex = 1
        Else
            pt.CacheIndex = ptMain.CacheIndex
        End If
    Next pt
Next wks
End Sub

Sub RefreshSlicersAndPivots()
ThisWorkbook.RefreshAll
Call ManageSlicers("disconnect")
Call UpdatePivotCache
Call ManageSlicers("connect")
End Sub

Upvotes: 2

Related Questions