Code for Slicer

[Veeru]

Hello,

i am looking for a code for my slicer.....where i m presenting data on 2 differnt sheets...one is singleview and other one is Comparison view.

Idea is if select on element in slicer ., lets say 2016-Actuals, it should lead to single view sheet, where ifwe select 2 elements in Month slicer, it should go to comparsion sheet.

Sample sheet attached..Thanks in advace

[Bob Phillips]

Put this code in the Pivot worksheet code module


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Const CONTROL_PIVOT As String = "PivotTable2"
Dim sc As SlicerCache
Dim si As SlicerItem
Dim cnt As Long

On Error GoTo wp_exit

Application.EnableEvents = False

With ThisWorkbook

With .SlicerCaches("Slicer_Dept.")

For Each si In .SlicerItems

cnt = cnt - si.Selected
Next si
End With

If cnt > 1 Then

.Worksheets("Compariosn View").Activate
Else

.Worksheets("Single View").Activate
End If
End With

wp_exit:
Application.EnableEvents = True
End Sub

[Veeru]

Thank you but it is not working properly,,,reason being it not allowing me to select Dept.....whenever i select 1 month..it leads me to comparison sheet....and same goes for if i select 2 months

same issue if i deselect any selected month in slicer,,,

[Bob Phillips]

Change Slicer_Dept. to SLicer_Month in the code if you want that slicer to drive it.

[Veeru]

Thanks but now after changing to Month ...as i click on single month it leads to single view and if i select 2 months it leads me to comparion ...which is partially right as it doesnt aloow me to select any dept....

[Bob Phillips]

Detail exactly what the rules are for selection/viewing, all combinations.