Macro Help Needed - Link Pivot Table Filter to Regular Table AutoFilter

I'm new to excel macro and new to this forum, and I have found a lot of helpful material here already.

I need a macro that will automatically change a table's autofilter to the same selections/values of the pivot table filter when the pivot table's pivot filter is changed.

For Exaple:
I have a pivot table with a pivot filter Date in B3 with value 1/1/2011, 2/1/2011, and 3/1/2011

I have a table(at A18) with a autofilter Date with the same value 1/1/2011.2/1/2011. 3/1/2011.

What I would like to do is when I change the pivot filter in B3 (for example, select 1/1/2011 and 2/1/2011), the table autofilter will be set to 1/1/2011 and 2/1/2011.

I would prefer to have two-way capabilities, but I can work with only one-way change.


This is the code I came up with so far, it only works with single selections, but I would need multiple selection capabilities. I'm using Excel 2007

Sub test2()
Dim KEY As Variant: KEY = Sheets("Yesterday").Range("B3").Value
Select Case KEY
Case Is = "(All)"
Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1
Sheets("Yesterday").Range("A18").AutoFilter Field:=4
Case Else
Sheets("Yesterday").Range("A18").AutoFilter Field:=2, Criteria1:=1
Sheets("Yesterday").Range("A18").AutoFilter Field:=4, Criteria1:=Sheets("Yesterday").Range("B3").Value
End Select
End Sub


Thank you in advance for any help and insight!