Pivot Slicers

[slicer]

Dear All
Let me introduce myself, I am new to use the MACRO & tried to learn from Chanddo.com & tried to enhance my skills a lot.
So I would like to thanks to all who are supporting each other to make our day to day activities more convenient.
Here I want to bring your notice as per excel available features called as “Slicer” we can select the data in the pivot table.
the same thing I tried to select the slicer value based on the “Cell-value”
the current status is as follows, if we select the cell value through dropdown list as.
IT --à Slicer IT is selected
MKT --à Slicer MKT is selected
But the requirement is to de-select or clear manual filter (All filters) to show the consolidated count
For more details please refer the attached sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Dim SI As SlicerItem
ExitSub:
If Not Intersect(Target, Range("B3")) Is Nothing Then
With ActiveWorkbook.SlicerCaches("Slicer_MKT_IT")
.ClearManualFilter
'End If
For Each SI In .SlicerItems
SI.Selected = UCase(SI.Value) = UCase(Range("B3").Value)
Next SI
End With
End If
End Sub

 

[Ajesh]

Hi Slicer,

Welcome to the forum.

Try this:

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

Thanks

 

[slicer]

Hi Slicer,
Welcome to the forum.
Try this:

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

Thanks

Dear Ajesh,
Thanks for your feedback, i have tried, but the the actual result is same, actually the requirement is after delete the value in reference cell ("B3") the Graph is should show values for both (MKT & IT), but the it is showing Blank,
can you please try the code in attached sheet & pest the full code in thread.
Thanks in Advance.

 

[Ajesh]

Hi Slicer,

If you add "ALL" to your Data VAlidation list in Cell B3 you don't need to make any changes to your code to get values for both MKT and IT.

Please see enclsoed.

Thanks

 

[slicer]

Dear Ajesh,
Once again, very big thanks for your feedback, actually this method is working fine, but the main challenge is that based on the V3 value i.e. MKT/IT I am using this to filter the contents from the table, hence if the V3 value changed as "ALL" the filter results are not displaying,

hope there may be some alternative solution which i am unable to find ....

Hi Slicer,
If you add "ALL" to your Data VAlidation list in Cell B3 you don't need to make any changes to your code to get values for both MKT and IT.
Please see enclsoed.
Thanks

 

[Ajesh]

I am not able to access your codes. I guess it is password protected.

Edit: Worked on the file uploaded by you initially. I have added a Shape and assigned a macro to clear all Slicer cache and set Cell B3 = "" when you click on it.

Please see enclosed if this helps.

Thanks

 

[slicer]

I am not able to access your codes. I guess it is password protected.

Edit: Worked on the file uploaded by you initially. I have added a Shape and assigned a macro to clear all Slicer cache and set Cell B3 = "" when you click on it.

Please see enclosed if this helps.

Thanks

Dear Friend,
Thanks for your feedback & support, now i am able to clear the many slicer in one click to reduce repeated task & hence saving in time as well.

 

[Ajesh]

Glad to help.