• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VBA to Auto refresh an advanced Filter

Andrew D

New Member
Hi All,


I was wondering if anyone can help with the following:


I have set up an advanced filter in Excel 2007 to filter data from a master spreadsheet (Download) into seperate sheet (MI)


The Advanced Filter details look like this:


List Range: Download!$A$1:$AK$100

Criteria range: MI!Criteria

Copy to: MI!Extract


Is there a way to use VBA to auto refresh this filter if new data is added into the Download sheet?


I have very little VBA knowledge so any advice woul be great.


Thanks,


Andrew
 
You'll probably want to limit how often this macro gets fired. Currently, it goes off if ANYTHING gets changed, but here's the layout:

[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'This code will get fired if ANYTHING gets changed
'may need to add some additional criteria to limit this

Application.EnableEvents = False

'Advanced filter code
Worksheets("Download").Range("$A$1:$AK$100").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("MI").Range("Criteria"), _
CopyToRange:=Worksheets("MI").Range("Extract"), Unique:=False

Application.EnableEvents = True
End Sub
[/pre]
 
Back
Top