title: Filter a slicer via a cell value
body: <p><strong>I want to be able to filter the slicer by the content of a cell value which I type manually in the worksheet.</strong></p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>slicer_Country_of_origin")
          .SlicerItems("ALBANIA").Selected = True
          .SlicerItems("AFGHANISTAN").Selected = False
          .SlicerItems("ALGIERIA").Selected = False
          .SlicerItems("ARMENIA").Selected = True
      End With
  End Sub
  </code></pre>
  
  <p>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".</p>
  
  <pre><code>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
  </code></pre>
  
  <p>After starting this macro, after the line 
  <code>sub Macro6()</code> 
  I recieved the message: </p>
  
  <blockquote>
    <p>Compile error: Expected End Sub.</p>
  </blockquote>
  
  <p>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.**</p>