Advanced filter refresh after data change

[Goog]

I have an advanced filter in Excel 2002 (on Windows XP). I want to automatically refresh/reapply the filter when any of the data (aka "List Range") is changed. I've done a lot of searching and experimentation and I just can't find a good solution. Please let me know if you have any thoughts or suggestions.

Thanks,

Marc

 

[VoG]

Take a look at the Worksheet_Change event.

 

[Goog]

Ok, after a few hours of messing around in VBA and searching the net, I've found a solution. Maybe it will help someone else in my position so I will post about it here:

My advanced filter situation: I have the source data on one tab, the criteria on another, and the extract range on a third tab (I am copying the filter results not filtering in place). I think this is what was making the vba so difficult for me. My file is called "Pipeline 200.xls" and I have created a named range for my source data (called Database), for my filter criteria (called Criteria), and for my extract (called Extract). I am using Excel 2002.

I created a macro called refresh_data that looks like this:

Sub refresh_data()

    Range("'Pipeline 200.xls'!Database").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("'Pipeline 200.xls'!Criteria"), CopyToRange:=Range( _
        "'Pipeline 200.xls'!Extract"), Unique:=False

End Sub

And in the tab with my source data (and the named range called Database) I have put the following code (by right clicking the tab and going to "view code"):

Private Sub Worksheet_Change(ByVal Target As Range)
    
    refresh_data

End Sub

For whatever reason, if I put the macro code directly in the Worksheet_Change function of the worksheet, it gives me errors.

Hope this helps someone else.

Marc