Macro with Autofilter Rule

[jevi]

Hi All,

I have registered this macro but is not taking correctly the autofilter rule (criteria). I need to filter the data based on column X1 (data are from A1:A200 but may vary everyday the row of data) and have only the values that are <> "OK". I try to write it in the macro but is not working.

Please any help?

Thank you,

Range("X1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=24, Criteria:="<>OK", _

Macro1 Macro
'

'
    Windows("CDatabase.xlsx").Activate
    Columns("A:W").Select
    Selection.Copy
    Windows("C Report.xlsm").Activate
    Columns("A:W").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("X1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$X$200").AutoFilter Field:=24, Criteria:="<>OK", _
    Sheets("DATABASE").Select
    Columns("A:X").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Sheets.Add After:=ActiveSheet
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B7").Select
    Sheets("P").Select
    Windows("C Database.xlsx").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

 

[kevin9999]

Try changing

Criteria:="<>OK"

To

Criteria1:="<>OK"

 

[Alex Blakenburg]

In addition to @kevin9999's change you also need to remove the ", _" from then end of the line.

 

[jevi]

Thank you both, it worked great. Another question as I have a limit of row here, how can I say till the data finishes as the row might be different (for sure I can do 10000 rows max that can happen) but I am curios to know the VBA language . ActiveSheet.Range("$A$1:$X$200").

 

[Alex Blakenburg]

If your data starts in A1 then you can probably just use Activesheet.Range("A1").CurrentRegion
Current region will include everything until it hits a fully blank row or fully blank column.

 

[jevi]

Ah Ok, super. So now I know it and I can use it in the other VBA. Thank you so much for explianing that to me.

 

[Alex Blakenburg]

You're welcome. Glad I could help.