How to autofilter with variable criteria?

Question

I am trying to get an array or range of values and for each of them to filter all data in a sheet and copy it to a new sheet.

I have tried many ways. I receive an error by the Autofilter method when I input a variable parameter instead of a string as Criteria.

Sub Macro1()
    Dim Cll As Range
    For Each Cll In Selection
        Columns("A:A").Select
        Selection.AutoFilter Field:=1, Criteria:=Cll.Value  '‹- here I get the error
        Cells.Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
    Next Cll  
End Sub

Error:

run-time error "1004"
Application-defined or object-defined error

The selection is a list of cells, each one containing a text, which should be the filtering criteria.

The column does not have a filter.

This is what my workbook looks like. Column A is to be filtered while on column B I wrote the list of filtering criteria I would like to use.

If the error like this occur, check the Tools -> references in your VBA project. — Geographos, Dec 01 '20 at 11:57
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Dec 01 '20 at 13:47
@Rory the selection is a list of cells, each one containing a text, which should be the filtering criteria — Massimo Dal Monico, Dec 09 '20 at 10:15
Yes, but what **specifically** is in the cell that causes the error? — Rory, Dec 09 '20 at 10:17
@Rory I'm not sure how to reply to your answer. I edited my question so you can see how the workbook looks like. The filter is applied to column A. I tried to position on cell A1 as well as on cell A4 but nothing changed. If you are talking about the content of the cells used as criteria, they are texts. If you could provide a similar example that works for you it would be enough for me. Thank you — Massimo Dal Monico, Dec 10 '20 at 16:34

score 0 · Accepted Answer · answered Dec 10 '20 at 17:11

It is likely your issue is caused by the fact that you are changing the active sheet and relying on selection, and you should be using criteria1, not criteria. Try avoiding selecting ranges that you don't need to:

Sub Macro1()
    Dim criteriaRange As Range
    Set criteriaRange = Selection
    Dim filterRange As Range
    With ActiveSheet
        .AutoFilterMode = False
        Set filterRange = .Range("A4:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    Dim Cll As Range
    For Each Cll In criteriaRange.Cells
        filterRange.AutoFilter Field:=1, Criteria1:=Cll.Value  '‹- here I get the error
        filterRange.Copy
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
    Next Cll
End Sub

How to autofilter with variable criteria?

1 Answers1