Last Updated: February 25, 2016
· punksteez

How to automate copying/updating filtered data from one spreadsheet to another.

It's absolutely inexplicable why Excel doesn't auto-update an advanced filter once it's created or maybe there's a way to do it I just wasn't able to find but this solution seems to the prevailing one on the interwebz.

Open your VBA editor and drop this code in as attached to the WorkSheet | SelectionChange event. Obviously update it with your own sources, targets and criteria fields.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Sheets("Sheet1").Range("A1:C4").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Sheet1").Range("E1:E2"), _
        CopyToRange:=Sheets("Sheet2").Range("A1:B4"), _
End Sub

Hope it helps!