VBA: Getting a reference value from another sheet as a criteria for filter

Question

I have two sheets - "Criteria" and "temporary".

I want to apply filter in Criteria sheet where the criteria will be taken from the active cell of "temporary" sheet. My below macro is failing for some reason.

Sub FilterTest1()
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "temporary"

ActiveSheet.[A:A].Value = Worksheets("Criteria").[A:A].Value

Cells.RemoveDuplicates Columns:=Array(1)

Range("A1").EntireRow.Delete

Dim B As Integer
B = Cells.CurrentRegion.Rows.Count

With Sheets("Temporary")
Selection.End(xlDown).Select
End With


With Sheets("Criteria")

.Range("$A:$B").AutoFilter
   .Range("$A:$B").AutoFilter field:=1, Criteria1:=Worksheets("temporary").ActiveCell.Value, Operator:=xlOr

End With

End Sub

The macro ends with a

run time error 438 "Object doesnt support this property or method.

whats the right method to do?

Adding further info: the objective is to change the criteria one by one. Right now it should select the last active cell in Temporary!A:A but then later i would be offsetting the selection upwards in Temporary!A:A and will be taking each active cell sheet as a criteria for filtering.

In which line does the error occur? • Note: Excel has more rows than Integer can handle: It is recommended always to use Long instead of Integer in VBA. — Pᴇʜ
@Pᴇʜ The error is on the 3rd row from bottom where the criteria is mentioned — Sagar Joshi

Unknown Unknown · Accepted Answer · 2018-07-02T06:39:52

It would be easier to apply the last value in Temporary!A:A to a var than rely on Select and Selection.

However, all,of your code seems to boil down to the AutoFilter taking the last value in Criteria!A:A as the filter. Adding a new worksheet, copying over the values from Criteria!A:A, removing duplicates and then selecting the last populated cell in Temporary!A:A does nothing to change that. If you sorted the data in Temporary!A:A it might change, but nothing you are currently doing is changing the criteria from the populated last value in Criteria!A:A.

Sub FilterTest1()
    dim c as variant
    with workSheets("Criteria")
        c = .cells(.rows.count, "A").end(xlup).value
        .Range("A:B").AutoFilter  field:=1, Criteria1:=c
    End With
End Sub

VBA: Getting a reference value from another sheet as a criteria for filter

1 Answers