VBA code to remove pivot slicers

[Karthik Thandapani]

Hi,

I am working on a dashboard requires fresh pivot & slicers every single day. Can anyone please suggest an idea how to clear/delete existing slicers along with pivot table?

P.S: presently the below code only clears the pivot but not its slicers.

Dim WB As Workbook, ws As Worksheet, pt As PivotTable
    If ActiveWorkbook Is Nothing Then
        MsgBox "There is no active workbook!", vbExclamation, "ERROR!"
        Exit Sub
    End If
    If MsgBox("Delete ALL pivot tables in the active workbook?", _
        vbYesNo + vbDefaultButton2, "DELETE ALL?") = vbNo Then Exit Sub
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
          pt.TableRange2.Clear
            'WS.Range(PT.TableRange2.Address).Delete Shift:=xlUp
        Next pt
    Next ws

Thanks,
Karthik

 

[Luke M]

The Slicers are not actually a part of the PivotTable object, they have their own collection. You could tack this onto the end of your current macro.

    Dim SC As SlicerCache
    For Each SC In ActiveWorkbook.SlicerCaches
        SC.ClearManualFilter
    Next SC

EDIT: Just saw that you are actually deleting the PT rather than clearing filters (not sure why...). If you want to remove the slicer all-together, it would be:

SC.Delete

 

[Karthik Thandapani]

Doesn't work!

Dim WB As Workbook, WS As Worksheet, PT As PivotTable
If ActiveWorkbook Is Nothing Then
MsgBox "There is no active workbook!", vbExclamation, "ERROR!"
Exit Sub
End If
If MsgBox("Delete ALL pivot tables in the active workbook?", _
vbYesNo + vbDefaultButton2, "DELETE ALL?") = vbNo Then Exit Sub
On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.TableRange2.Clear
'WS.Range(PT.TableRange2.Address).Delete Shift:=xlUp
Next PT
Next WS

Dim SC As SlicerCache
For Each SC In ActiveWorkbook.SlicerCaches
SC.ClearManualFilter
Next SC

Dim oSlicer As Slicer
Dim oSlicercache As SlicerCache
Dim oPT As PivotTable
Dim oSh As Worksheet
For Each oSlicercache In ThisWorkbook.SlicerCaches
For Each oPT In oSlicercache.PivotTables
oPT.Parent.Activate
'MsgBox oSlicercache.Name & "," & oPT.Parent.Name
Next
Next

Dim i As SlicerCaches
Dim j As Slicers
Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches

j("Country").Delete
j("Report type / name").Delete

Dim slcr As SlicerCache

For Each slcr In ActiveWorkbook.SlicerCaches

slcr.ClearManualFilter
Next slcr

 

[Luke M]

Could you explain more what "Doesn't work" means. It doesn't tell us much information. Code won't run, code causes errors, code doesn't produce right results...

Also, why do you have so many more sections in your code??

 

[Deepak]

Doesn't work!
...
....
Next slcr

You must have to use code tags.
Pls edit the same.