VBA for automatic selection of Slicer on changing TABs.

[samirdey]

Hi All,

I am trying to get a vba for selecting slicer automatically while changing tab. I have recorded macro but it is not working. Also, I want the macro to work whenever the workbook is opened and the different TAB is selected. Below is the recorded macro:

Sub Test()
'
' Test Macro
'

'
Sheets("CCR").Select
With ActiveWorkbook.SlicerCaches("Slicer_Dept")
.SlicerItems("CCR").Selected = True
.SlicerItems("BUS").Selected = False
.SlicerItems("BUS TECH").Selected = False
.SlicerItems("RES CARE").Selected = False
.SlicerItems("TECH").Selected = False
.SlicerItems("").Selected = False
.SlicerItems(" ").Selected = False
.SlicerItems("(blank)").Selected = False
End With
Sheets("BUS").Select
With ActiveWorkbook.SlicerCaches("Slicer_Dept")
.SlicerItems("BUS").Selected = True
.SlicerItems("BUS TECH").Selected = False
.SlicerItems("CCR").Selected = False
.SlicerItems("RES CARE").Selected = False
.SlicerItems("TECH").Selected = False
.SlicerItems("").Selected = False
.SlicerItems(" ").Selected = False
.SlicerItems("(blank)").Selected = False
End With
Sheets("BUS TECH").Select
With ActiveWorkbook.SlicerCaches("Slicer_Dept")
.SlicerItems("BUS TECH").Selected = True
.SlicerItems("BUS").Selected = False
.SlicerItems("CCR").Selected = False
.SlicerItems("RES CARE").Selected = False
.SlicerItems("TECH").Selected = False
.SlicerItems("").Selected = False
.SlicerItems(" ").Selected = False
.SlicerItems("(blank)").Selected = False
End With
Sheets("TECH").Select
With ActiveWorkbook.SlicerCaches("Slicer_Dept")
.SlicerItems("TECH").Selected = True
.SlicerItems("BUS").Selected = False
.SlicerItems("BUS TECH").Selected = False
.SlicerItems("CCR").Selected = False
.SlicerItems("RES CARE").Selected = False
.SlicerItems("").Selected = False
.SlicerItems(" ").Selected = False
.SlicerItems("(blank)").Selected = False
End With
Sheets("RES CARE").Select
With ActiveWorkbook.SlicerCaches("Slicer_Dept")
.SlicerItems("RES CARE").Selected = True
.SlicerItems("BUS").Selected = False
.SlicerItems("BUS TECH").Selected = False
.SlicerItems("CCR").Selected = False
.SlicerItems("TECH").Selected = False
.SlicerItems("").Selected = False
.SlicerItems(" ").Selected = False
.SlicerItems("(blank)").Selected = False
End With
End Sub[/CODE]

Please help.