How do I set data table filter equal to pivot table filter

Question

I have two tables - one with raw data - the other is a pivot table. I need to be able to link the values, sometimes multiple values of the Data Table and the Pivot Table. Both have slicers and those slicers need to match.

However, slicers are explicit in defining which values are True or False - I was hoping to use the filter function instead. Something like this:

Worksheets("Opportunity Details").ListObjects("RawData").Range.AutoFilter Field:=9, Criteria1:= _
   Worksheets("PIVOTDATE").PivotTables("OppPivot2").PivotFields("Record Manager").Range

Obviously this syntax doesn't work - I'm hoping there's some easy way to just set one equal to the other though.

iontom iontom · Accepted Answer · 2015-04-02T15:49:26

Alright, this wasn't easy to find but the solution is pretty elegant:

With ActiveWorkbook
    For Each oSi In OD_am.SlicerItems
        On Error Resume Next
        If OP_am.SlicerItems(oSi.Value).Selected <> OD_am.SlicerItems(oSi.Value).Selected Then
            OD_am.SlicerItems(oSi.Value).Selected = OP_am.SlicerItems(oSi.Value).Selected
        End If
    Next
End With

Pretty much a For loop comparing SlicerItems line by line. No need to explicitely name each part. I was worried that a slicer for apivot table and for a normal table wouldn't communicate right - but it works!

Thanks to references from this: http://www.jkp-ads.com/articles/slicers04.asp

How do I set data table filter equal to pivot table filter

1 Answers