GET SLICER VALUES FROM CELL VALUE

I've looked around for hours to try and find something that I could try to adapt to my needs but not really found anything close yet, I'll keep trying though. Most of what Ive seen so far is the opposite, getting slicer values into a cell

I have 6 groups of 6 slicers where I want each slicer to have a value which is automatically selected from it's own cell (comma separated values) or a range of cells when a group is called/selected. The slicer values can then be changed if desired or the accompanying graphs/charts simply viewed. Each time a group is called/selected it will use their own cell or range values to select the slicer values so no changes are saved.

The idea behind this is that the cell values hold the optimum settings for the slicers which potentially change over time therefore the ability to change the slicers and experiment with different settings is what is key (I'll be able to add in code to automate cell values later) All data is linked to the power pivot data model The workbook I'm using is large but the slicers only have between 2 and 20 possible values in each. When I reset them at the moment it takes about 20 seconds, it is what it is. I'm hoping I don't need to create a new workbook to attach here as that could prove troublesome and time consuming but if that's what needs to be done then so be it. Ideally I would like someone to point me to somewhere out there on the wild web where I could adapt some code for myself, I'm very slow and clunky at it but I kind of get somewhere near eventually. Thanks in advance.

I'll take a look Carim, thanks for your help.

I've found some code that looks more like what I want but there seems to be at least one error so far.

'At least one item must remain visible in the Slicer at all times, so make the first

'item visible, and at the end of the routine, check if it actually *should* be visible

.SlicerItems(1).Selected = True

Returns the the run time error 1004, application-defined or object error

I had to define pt and I guess dim as PivotTable is correct.

The line vSelection = Array("Class2", "Class3", "Class4") is something I would like to pick up from cells on the sheet and was wondering if that would be possible

Option Explicit

Sub FilterSlicer()
Dim slr As Slicer
Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Long
Dim vItem As Variant
Dim vSelection As Variant
Dim pt As PivotTable

Set sc = ActiveWorkbook.SlicerCaches("Slicer_Class1")

vSelection = Array("Class2", "Class3", "Class4")

For Each pt In sc.PivotTables
    pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
Next pt

With sc

    'At least one item must remain visible in the Slicer at all times, so make the first
    'item visible, and at the end of the routine, check if it actually  *should* be visible
    .SlicerItems(1).Selected = True

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .SlicerItems.Count
        If .SlicerItems(i).Selected Then .SlicerItems(i).Selected = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vSelection
        .SlicerItems(vItem).Selected = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the countries of interest
    On Error Resume Next
    If InStr(UCase(Join(vSelection, "|")), UCase(.SlicerItems(1).Name)) = 0 Then .SlicerItems(1).Selected = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Slicer, so I have cleared the filter"
    End If
    On Error GoTo 0
End With


For Each pt In sc.PivotTables
    pt.ManualUpdate = False
Next pt

End Sub

I'll make a mock up this evening when I get back as the master file is too large. Thanks for your continued help.

I hope the file helps and that I've explained myself clearly enough for you to understand what I'm trying to do! If you are able to solve the puzzle or part of it would you be kind enough to add some notes for me to understand how it works?

Am I right in thinking that because of the use of a data model all selected items must be visible in the pivot table before code runs to avoid an error? Would this just mean clearing all filters beforehand?

In the meantime I'll keep looking and trying to understand.

Ok, it works if I hard code which items to display so I've been trying to come up with a way forward that involves using a cell reference as the array for the filtered items on the slicer. I'm struggling to get the code to run using a string variable as the array. I found something here that may be a way forward - https://www.mrexcel.com/board/…array-as-an-array.935150/

I don't understand what's happening here so would appreciate any pointers from someone. I've attached an updated file which I hope helps.

Slicer2.xlsm