CODEWITHSUNDEEP
excelvba
Gosia Jankowska
Gosia Jankowska

Reputation: 1

Filter a slicer via a cell value

I want to be able to filter the slicer by the content of a cell value which I type manually in the worksheet.

I have slicer "country_of_origin" that controls three pivot tables "applications", "decisions", "invitations". I would like to filter the slicer by the "country of origin" which I type manually in the cell B2, without scrolling the whole list of countries on the slicer list.

I tried to record macro, but it was not a solution for me, because it won't work, if I typed the name of the country manually in the cell in the worksheet. It looked like that:

slicer_Country_of_origin")
        .SlicerItems("ALBANIA").Selected = True
        .SlicerItems("AFGHANISTAN").Selected = False
        .SlicerItems("ALGIERIA").Selected = False
        .SlicerItems("ARMENIA").Selected = True
    End With
End Sub

Further I found on mrexcel.com forum some code, I copied it, changing the name of slicer to "country of origin" and position of the cell to "$b$2".

sub Macro6()   
' sheet module
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim sc As SlicerCache, si As SlicerItem
        If Target.Address = "$B$2" Then                    
            Set sc = ActiveWorkbook.SlicerCaches("Country of origin")      ' desired slicer
            sc.ClearAllFilters
            For Each si In sc.SlicerItems
                If si.Caption = CStr(Target) Then
                    si.Selected = True
                Else
                    si.Selected = False
                End If
            Next
        End If
    End Sub

After starting this macro, after the line sub Macro6() I recieved the message:

Compile error: Expected End Sub.

I would like to transform/correct the code, so it will meet my needs, which is to filter the slicer by the content of a cell value which I type manually in the worksheet.**

Upvotes: 0

Views: 6602

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Your code is not valid. It must be

sub Macro6()   
' sheet module
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sc As SlicerCache, si As SlicerItem
    If Target.Address = "$B$2" Then                    
        Set sc = ActiveWorkbook.SlicerCaches("Country of origin")      ' desired slicer
        sc.ClearAllFilters
        For Each si In sc.SlicerItems
            If si.Caption = CStr(Target) Then
                si.Selected = True
            Else
                si.Selected = False
            End If
        Next
    End If
End Sub

Note that Worksheet_Change is an event and does not appear in the list where you can run macros. This macro cannot be run manually it automatically runs every time a cell is changed.

Upvotes: 1

Related Questions