Blair246810
11-03-2016, 12:22 PM
I need to know if there is a way to disconnect and reconnect slicers from Pivot Tables. I have created a Pivot Table workbook that copies Pivot Tables and charts to a new sheet. This being the case, the name of the slicer will always change so I wanted to know if there is a way to disconnect and reconnect without using the slicer formula name.
The exact message is "Runtime error 5, Invalid procedure call or argument".
My version of Microsoft Office is 2010 and the operating system is Windows 7 Professional.
The code below is a work (hopefully) in progress
Sub DisconnectSlicers()
Dim PT As PivotTable
Dim sl As Integer
Dim slcr As Slicer
ActiveSheet.Shapes(4).Select
With ActiveWorkbook.ActiveSlicer.Shape
End With
With ActiveWorkbook.ActiveSlicer
Set slcr = ActiveWorkbook.ActiveSlicer
End With
'The code above will select the slicer, but it does not activate it in a way that will allow me to disconnect the slicer with the code
'below. The code below will disconnect the slicer, but only if I use the name of the slicer. Is there a code that can take the code for selecting a slicer (used 'above) and transition it to select the slicer in such a way that it will allow the code below to work?
With ActiveWorkbook.SlicerCaches("Slicer_Region_Manager35").PivotTables
For sl = .Count To 1 Step -1
.RemovePivotTable (.Item(sl))
Next
End With
End Sub
The exact message is "Runtime error 5, Invalid procedure call or argument".
My version of Microsoft Office is 2010 and the operating system is Windows 7 Professional.
The code below is a work (hopefully) in progress
Sub DisconnectSlicers()
Dim PT As PivotTable
Dim sl As Integer
Dim slcr As Slicer
ActiveSheet.Shapes(4).Select
With ActiveWorkbook.ActiveSlicer.Shape
End With
With ActiveWorkbook.ActiveSlicer
Set slcr = ActiveWorkbook.ActiveSlicer
End With
'The code above will select the slicer, but it does not activate it in a way that will allow me to disconnect the slicer with the code
'below. The code below will disconnect the slicer, but only if I use the name of the slicer. Is there a code that can take the code for selecting a slicer (used 'above) and transition it to select the slicer in such a way that it will allow the code below to work?
With ActiveWorkbook.SlicerCaches("Slicer_Region_Manager35").PivotTables
For sl = .Count To 1 Step -1
.RemovePivotTable (.Item(sl))
Next
End With
End Sub