Help with Slicer name appearing on a Cell

[Sk123]

Hello,

I'm trying to get my slicers name to appear on cell "D1", and the name should change when i click on different slicer option. Basically when i click onto Sales on the slicer, the name sales should appear on cell D1, and when i click onto Cost, the name cost should appear on Cell D1, and so on. Also, there is no pivot table or table involved here. I just need slicer name to appear on a cell. Here is the code:

Sub Slicer_test()

Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Banner")
Dim sItem As Excel.SlicerItem
For Each sItem In cache.SlicerItems
If sItem.Selected = "HBC" Then Range("D1") = "Sales"
ElseIf sItem.Selected = "L&T" Then Range("D1") = "Cost"
ElseIf sItem.Selected = "SAKS" Then Range("D1") = "Profit"

End If


End Sub

 

[GraH - Guido]

Basic question: how did you manage to put a slicer on your sheet without pivot or table?

 

[Sk123]

Basic question: how did you manage to put a slicer on your sheet without pivot or table?

thank you for your reply. I figured it out

 

[p45cal]

Basic question: how did you manage to put a slicer on your sheet without pivot or table?

Excel 2016 allows slicers on Tables too.

 

[NARAYANK991]

Excel 2016 allows slicers on Tables too.

Hi ,

I think this statement in the OP is ambiguous / confusing :

Also, there is no pivot table or table involved here.

Narayan

 

[p45cal]

Oh, well spotted!
I'm being thick.

 

[GraH - Guido]

Excel 2016 allows slicers on Tables too.

I did say pivot or table as well ;-)