Slicers and Pivot Tables

ADSkinner072

New Member
Joined
Jun 2, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Good morning,
I have a sheet I am working on that has slicers and pivot tables that will refresh daily on new data from a newly created table. The code below is what I am using to create my new sheet as a table and name it as "Data", remove the connections to the slicers, and then changing the data source on the pivot tables to look at the Data source "Data"

The problem I am having is when I go back to reconnect the slicers, as none of my pivot tables are listed as options to reconnect. Is there an easier way or better way I could be doing this sequence?

Thank you very much for your time


VBA Code:
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lRow, lCol)), , xlYes).Name = "Data"

Sheets("Team Performance").Select
    ActiveSheet.Shapes.Range(Array("New/Open")).Select
    ActiveWorkbook.SlicerCaches("Slicer_New_Open").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable4"))
    ActiveWorkbook.SlicerCaches("Slicer_New_Open").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable6"))
    ActiveWorkbook.SlicerCaches("Slicer_New_Open").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable7"))
    ActiveWorkbook.SlicerCaches("Slicer_New_Open").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable8"))
    ActiveSheet.Shapes.Range(Array("OrderedByGroup")).Select
    ActiveWorkbook.SlicerCaches("Slicer_OrderedByGroup").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable4"))
    ActiveWorkbook.SlicerCaches("Slicer_OrderedByGroup").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable5"))
    ActiveWorkbook.SlicerCaches("Slicer_OrderedByGroup").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable6"))
    ActiveWorkbook.SlicerCaches("Slicer_OrderedByGroup").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable7"))
    ActiveWorkbook.SlicerCaches("Slicer_OrderedByGroup").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable8"))
    ActiveSheet.Shapes.Range(Array("Product Code 1")).Select
    ActiveWorkbook.SlicerCaches("Slicer_Product_Code1").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable4"))
    ActiveWorkbook.SlicerCaches("Slicer_Product_Code1").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable5"))
    ActiveWorkbook.SlicerCaches("Slicer_Product_Code1").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable6"))
    ActiveWorkbook.SlicerCaches("Slicer_Product_Code1").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable7"))
    ActiveWorkbook.SlicerCaches("Slicer_Product_Code1").PivotTables. _
        RemovePivotTable (ActiveSheet.PivotTables("PivotTable8"))
    ActiveSheet.Shapes.Range(Array("Warehouse")).Select
    ActiveWorkbook.SlicerCaches("Slicer_Warehouse").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable4"))
    ActiveWorkbook.SlicerCaches("Slicer_Warehouse").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable5"))
    ActiveWorkbook.SlicerCaches("Slicer_Warehouse").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable6"))
    ActiveWorkbook.SlicerCaches("Slicer_Warehouse").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable7"))
    ActiveWorkbook.SlicerCaches("Slicer_Warehouse").PivotTables.RemovePivotTable ( _
        ActiveSheet.PivotTables("PivotTable8"))


VBA Code:
Dim pt As PivotTable
    For Each pt In ActiveWorkbook.Worksheets("Team Performance").PivotTables
    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:="Data")
    Next pt
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,136,765
Messages
5,677,615
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top