How can I AutoFilter using two criteria in a macro?

I'm trying to AutoFilter with two criteria the second is "Not Equal To" in Text.
The code works for one filtering but fails on the second can some assist me by changing the code attached.

Thanks
OverdueTest2.xlsm
user2073Asked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
You may need to turn auto filter off first.

Try this (See line 10):

Sub FiltersColD()
'This macro filters Col."D" for dates before Todays Date and Not have "Destroyed" in column "F".
'Then copies to worksheet "Overdue".

Dim Date2 As Date
Date2 = Now()

Sheets("Data1").Select

    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=4, Criteria1:="<=" & CStr(Date2), Operator:=xlFilterValues
    
    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=6, Criteria1:="<>Destroyed", Operator:=xlFilterValues
    
    Range("A2:A300, D2:D300").Select
    Selection.Copy
    Sheets("Overdue").Select
    Range("A3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

Open in new window


Dave
0
 
dlmilleCommented:
Your autofilter needs to extend all the way to column F.  Here it is corrected:

    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=4, Criteria1:="<=" & CStr(Date2), Operator:=xlFilterValues
    
    ActiveSheet.Range("$A$1:$F$50").AutoFilter Field:=6, Criteria1:="<>Destroyed", Operator:=xlFilterValues
    

Open in new window


See attached with this correction.

Cheers,

Dave
OverdueTest2-r1.xlsm
0
 
user2073Author Commented:
Thanks for the solution it works great, if I use your file or copy your file to another computer. But if I change my code to be the same as your worksheet or copy your code into my worksheet, the macro stops on the second line of criteria every time any suggestions.
0
 
user2073Author Commented:
You guys ar fabulous Thanks very much. The extra code works a treat.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.