Disconnect slicers, dynamically update pivot table data source range et reconnect

Hey everyone, I'm a noob trying to learn. I built a dashboard with charts and slicers made from pivot tables. I would love to be able to update the data source range automatically (which requires disconnecting all slicers, updating and then reconnecting everything) using VBA.

I found this piece of code but I don't really know how to use it...

Can you help me please?

Thank you very much,

vincent

Dim PT                          As PivotTable
   Dim ptMain                      As PivotTable
   Dim ws                          As Worksheet
   Dim oDic                        As Object
   Dim oPivots                     As Object
   Dim i                           As Long
   Dim lIndex                      As Long
   Dim Max                         As Long
   Dim vPivots
   Dim vSlicers
   Dim vItem

   vSlicers = Array("REG", "DIRECTOR", "AREA_MANAGER", "AOI", "DMA", "AG", "CLOCK")
   Set oDic = CreateObject("Scripting.Dictionary")

   Max = Sheets("eLink_Raw").Cells(Rows.Count, "A").End(xlUp).Row

   ' disconnect slicers
   For Each vItem In vSlicers
      With ActiveWorkbook.SlicerCaches("Slicer_" & vItem).PivotTables
         If .Count > 0 Then
            Set oPivots = CreateObject("Scripting.Dictionary")
            For i = .Count To 1 Step -1
               oPivots.Add .Item(i).Name, .Item(i)
               .RemovePivotTable .Item(i)
            Next i
            oDic.Add vItem, oPivots
         End If
      End With
   Next vItem

   ' update pivottables
   For Each ws In ActiveWorkbook.Worksheets
      For Each PT In ws.PivotTables
         If lIndex = 0 Then
            PT.ChangePivotCache _
                  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                    SourceData:=Sheets("eLink_Raw").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
            Set ptMain = PT
            lIndex = 1
         Else
            PT.CacheIndex = ptMain.CacheIndex
         End If
      Next PT
   Next ws

   ' reconnect slicers
   For Each vItem In vSlicers
      If oDic.Exists(vItem) Then
         Set oPivots = oDic(vItem)
         vPivots = oPivots.Items
         For i = LBound(vPivots) To UBound(vPivots)
            ActiveWorkbook.SlicerCaches("Slicer_" & vItem).PivotTables.AddPivotTable vPivots(i)
         Next i
      End If
   Next vItem

   Set oDic = Nothing

It looks like the only thing you need to change is this line:

vSlicers = Array("REG", "DIRECTOR", "AREA_MANAGER", "AOI", "DMA", "AG", "CLOCK")

to match the names of the slicers you need to disconnect.

Thanks

There seems to be other parameters I need to change like "eLink_Raw" in

Max = Sheets("eLink_Raw").Cells(Rows.Count, "A").End(xlUp).Row

Also I can't figure out what "Slicer_" mean in this line of code:

 With ActiveWorkbook.SlicerCaches("Slicer_" & vItem).PivotTables

Thanks for your help!

By default the slicer cache is called Slicer_ followed by the name of the field you attached the slicer to. What did you alter the array to?

thanks, I added my slicers.. for some reason the "Slicer_" seems to cause errors in my code

What does:

Msgbox Activeworkbook.Slicercaches(1).name

show?