CODEWITHSUNDEEP
excelvba
Rob
Rob

Reputation: 428

Trying to Filter Sheet using Cell in another Sheet

So my first sheet is the "Macro" button sheet which contains all of the Macros I use to manipulate data in the workbook and I'm trying to make a button that takes the input in a specific cell and then filter the sheet containing the data to only rows containing the input value.

I'm receiving an Run-time error '1004': AutoFilter method of Range class failed

The button contains this code:

Private Sub CommandButton1_Click()
    Dim lr As Long
    With ThisWorkbook.Sheets("Part List")
        lr = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        .Range("A1:E" & lr).AutoFilter field:=5, Criteria1:=ThisWorkbook.Sheets("Macro").Cells(1, 1).Value
    End With
End Sub

This is the 'Macro' Sheet Macro Page

This is the page that the button macro is attempting to filter enter image description here

Upvotes: 0

Views: 84

Answers (1)

HTH
HTH

Reputation: 2031

your criteria consist of one value ony, hence no array for it nor Operator:=xlFilterValues parameter

Private Sub CommandButton1_Click()
    Dim lr As Long
    With ThisWorkbook.Sheets("Part List")
        lr = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0).Row
        .Range("A1:A" & lr).AutoFilter field:=1, Criteria1:=ThisWorkbook.Sheets("Macro").Cells(1, 1).Value
    End With
End Sub

where I also adopted field:=1 since your "database" consists of 1 column only (Range("A1:A" & lr))

should you actually need to filter on column "E" (fifth column starting from A) then just change

.Range("A1:A" & lr).AutoFilter field:=1

to 

.Range("A1:E" & lr).AutoFilter field:=5

Upvotes: 1

Related Questions