title: VBA: Getting a reference value from another sheet as a criteria for filter
body: <p>I have two sheets - "Criteria" and "temporary".</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The macro ends with a </p>
  
  <blockquote>
    <p>run time error 438 "Object doesnt support this property or method.</p>
  </blockquote>
  
  <p>whats the right method to do?</p>
  
  <p>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.</p>