Selecting multiple slicer items based on a cell value

Question

I need a VBA code that is able to select multiple slicer items based on a cell value. This is for making weekly reports so the target cell would be C17 which is in format "YYYYWW" (i.e 3rd week of 2018 would be 201803) and is entered by user. This is what I call endDate. Excel itself then calculates the starting week, which is by default 30 weeks before endDate. As the value of cell C17 changes, the macro should automatically trigger.

So, for an example, "201803" is entered into C17 and excel then calculates the value "201725" and inserts it into G17. Entering a new value into C17 triggers the macro, which start off by reading the value of cell C17 (endDate) and G17 (startDate). Then the macro will deselect all slicer items (there is only one slicer used) and select those which are either equal to endDate or startDate, or in between the two values. Because there was 52 weeks in 2017, "201753" should be overwritten into "201801". I know this is not the ideal solution as it needs to be updated every year but it's the best I came up with.

I have learned little programming in C but this is one of my first codes in VBA so any pointers would be appreciated. This is what I got so far:

Private Sub Worksheet_Change(ByVal Target As Range)

'  18.01.2018 Oscar E.

    Dim startDate As Long
    Dim endDate As Long
    Dim n As Long
    Dim i As Long

    If Target.Address = "$C$3" Then
        endDate = Range("C17").Value
        startDate = Range("G17").Value
        With ActiveWorkbook.SlicerCaches("Slicer_YYYYWW")
            .ClearManualFilter
            For n = startDate To endDate
                If n = 201753 Then
                    n = 201801
                End If
                .SlicerItems(n).Selected = True
            Next
        End With
    End If
End Sub

Right now, I am getting a

Run time error '1004': Application-defined or object defined error

on .SlicerItems(n).Selected = True near the end. So the problem is, I guess, overwriting slicer items selection.

Dy.Lee Dy.Lee · Accepted Answer · 2018-01-18T13:57:42

SlicerItems must to be Stirng. But when .ClearManualFilter all item selected. All items must not be selected.

Dim startDate As Long
Dim endDate As Long
Dim n As Long
Dim i As Long
Dim strN As String
Dim sl As SlicerItem

If Target.Address = "$C$3" Then
    endDate = Range("C17").Value
    startDate = Range("G17").Value
    With ActiveWorkbook.SlicerCaches("Slicer_YYYYWW")
        .ClearManualFilter
        For n = startDate To endDate
            If n = 201753 Then
                strN = CStr(201801)
            End If
        Next
      For Each sl In .SlicerItems
        If sl.Name = strN Then
            sl.Selected = True
        Else
            sl.Selected = False
        End If
    Next
    End With
End If

Selecting multiple slicer items based on a cell value

1 Answers