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
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
Code:
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
Attachments
Last edited by a moderator: