I have 2 different data source but 1 common field. I have inserted 2 individual slicers for those 2 data sets. Now I want to create a vba code where I can manually enter a value in a particular cell and on that cell value both the slicers gonna filter. for example: I have common field name"Year" and "Quarter". these 2 fields are common in 2 different data set. Now I have inserted 2 slicers for Year and 2 slicers for Quarter. So I'd like to manually type in a cell 2020 and in another cell Q3 and all 4 slicers should get updated. Also if no value is matched for a particular data set then it should show all the value for that data I tried the following code but it's helpful only for 1 slicer at a time:
Sub Worksheet_Change(ByVal Target As Range)
Dim sc As SlicerCache, si As SlicerItem
If Target.Address = "$K$1" Then
Set sc = ActiveWorkbook.SlicerCaches("Fiscal Year") ' desired slicer
sc.ClearAllFilters
For Each si In sc.SlicerItems
If si.Caption = CStr(Target) Then
si.Selected = True
Else
si.Selected = False
End If
Next
End If
End Sub
Sub Worksheet_Change(ByVal Target As Range)
Dim sc As SlicerCache, si As SlicerItem
If Target.Address = "$K$1" Then
Set sc = ActiveWorkbook.SlicerCaches("Fiscal Year") ' desired slicer
sc.ClearAllFilters
For Each si In sc.SlicerItems
If si.Caption = CStr(Target) Then
si.Selected = True
Else
si.Selected = False
End If
Next
End If
End Sub