Slicer selection via VBA to make iteractive Dashboard

[mcfadwmc]

Hello,

I've created a dashboard containing data from 4 pivot tables which is tabulated and charted.
I'd like to make the dashboard interactive by having the names clickable to mirror the effect of slicing

For example, i have 3 slicers sites,region,product which are all linked to each of the 4 pivot tables so slicing one reduces all 4 pivots and affects the other two slicers.

What i want to do is bring the action of slicing away from the slicer and make anything on the table clickable to give single selection.

So if i'm in the table for sales by site and i click on the site, i want the product table to only show me products sold by that site. I can do this through the slicer but i want to do it via the cell click.

I have this code which will capture the selection but it doesn't update the slicer cache correctly and takes a lot of time to cycle through.

Any ideas


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(ActiveCell, [sitesl]) Is Nothing Then
[valsitesl] = ActiveCell.Value
ElseIf Not Application.Intersect(ActiveCell, [regionsl]) Is Nothing Then
[valregionsl] = ActiveCell.Value
ElseIf Not Application.Intersect(ActiveCell, [productsl]) Is Nothing Then
[valproductsl] = ActiveCell.Value
End If

Dim lIndex As Long
Dim lLoop As Long

With ActiveWorkbook.SlicerCaches("Slicer_Area")
lIndex = .SlicerItems.Count
For lLoop = 1 To lIndex
If (.SlicerItems(lLoop).Name) = Range("r44").Value Then
.SlicerItems(lLoop).Selected = True
Else
.SlicerItems(lLoop).Selected = False
End If
Next
End With

End Sub