Shows the values within the cells of a selected Slice items

[marreco]

Hi.

How get value in Slice and insert in cell?

 

[Hui]

The technique is discussed here
http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/

 

[marreco]

OK, thanks, but how do it based VBA ?
I try it, but return only last value.

Sub Tryed()
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("MySlicevalue")
Dim sItem As Excel.SlicerItem
For Each sItem In cache.SlicerItems
    Range("J1").Value = sItem.Name
Next sItem
End Sub

 

[NARAYANK991]

Hi ,

See if this works.

Sub TryOnceMore()
    Dim i As Integer
    Dim sItem As Excel.SlicerItem
    Dim cache As Excel.SlicerCache
    Set cache = ActiveWorkbook.SlicerCaches("MySlicevalue")

    i = 0
    For Each sItem In cache.SlicerItems
        Range("J1").Offset(i).Value = sItem.Name
        i = i + 1
    Next sItem
End Sub

Narayan

 

[marreco]

Hi Narayan, very good, but how show in cell, only seleted value in Slice?

 

[NARAYANK991]

Hi ,

The complete code for a UDF is here :

http://www.jkp-ads.com/articles/slicers05.asp

If you want a normal macro instead of a UDF , and if you want help , please post back.

Narayan

 

[marreco]

Very very nice, thank you!!!!!!!!!!!!!!!!!!!