CODEWITHSUNDEEP
excelvbaautofilter
Massimo Dal Monico
Massimo Dal Monico

Reputation: 13

How to autofilter with variable criteria?

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.

excel workbook

Upvotes: 1

Views: 1004

Answers (1)

Rory
Rory

Reputation: 34045

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

Upvotes: 0

Related Questions