Filter criteria in as a variable
I want to set the upper and lower criteria for the filter of column F to variables in H1 and H2 dropdowns.
The message box reads correctly, however, the only value returned to the destination ranges is the F1 value.
Hard coding the criteria into the code works okay.
Thanks.
Howard
Sub A_Filter_Copy()
Dim CriteriaU As Object
Dim CriteriaL As Object
Set CriteriaU = ActiveSheet.Cells(1, 8)
Set CriteriaL = ActiveSheet.Cells(2, 8)
'MsgBox CriteriaU & " " & CriteriaL
Application.ScreenUpdating = False
ActiveSheet.Range("F1", Range("F1").End(xlDown)).AutoFilter Field:=1, Criteria1:=CriteriaU, _
Operator:=xlAnd, Criteria2:=CriteriaL
ActiveSheet.AutoFilter.Range.Copy
Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Selection.AutoFilter
Application.ScreenUpdating = False
Application.CutCopyMode = False
End Sub
|