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

Filter criteria in as a variable
 

Hi Howard,

Am Thu, 4 Jun 2015 04:08:37 -0700 (PDT) schrieb L. Howard:

I want to set the upper and lower criteria for the filter of column F to variables in H1 and H2 dropdowns.

then you have to code =Criteria1 and <=Criteria2:

Sub A_Filter_Copy()

Dim CriteriaU As Double
Dim CriteriaL As Double

CriteriaU = ActiveSheet.Cells(1, 8)
CriteriaL = ActiveSheet.Cells(2, 8)

'MsgBox CriteriaU & " " & CriteriaL

Application.ScreenUpdating = False
With ActiveSheet
.Range("F1", .Range("F1").End(xlDown)).AutoFilter Field:=1, _
Criteria1:="=" & CriteriaU, Operator:=xlAnd, Criteria2:="<=" &
CriteriaL

.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

.AutoFilterMode = False
End With
Application.ScreenUpdating = False
Application.CutCopyMode = False

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Filter criteria in as a variable
 

I see, gotta have the & < in the fray.

It was in the hard coded version I was using from the recorder, but I failed to catch the need of them in the variable code.

Thanks Claus.

Howard