Control Slicer with VBA

[zodiaceuk]

Hi All,

Is it possible to control a slicer with VBA please?

I need the slicer to look for specific weeks.
These weeks are in CELs A1:A5

Wk1, Wk2 etc

The slicers control a couple of pivot tables.
I'm looking to find the weeks that are listed only.

Is this possible please?

 

[zodiaceuk]

Hi All,

I almost got this working, just need help getting the range to work.

Public Sub Test ()
Dim WK1 as String
Dim Item as SlicerItem

WK1 = Sheets("Control").Range("E24:E28")

For Each Item in ThisWorkbook.SlicerCaches("Slicer_Week2").SlicerItems
If Item.Name = WK1 then
Item.Selected = True
Else 
Item.Selected = False
End If 
Next Item

End Sub

This is giving a run-time error "13" - Type mismatch on:

WK1 = Sheets("Control").Range("E24:E28")​

I have tried replacing the : with a , this made no difference.

The macro works perfectly when I only use .Range("E24"), however I need to include a couple of CELs

((Week format have been changed to numbers only 01, 02 etc))

am I missing something silly?