Variable in AutoFilter criteria


M

morry

I want to let people custom filter a list by typing a date into tw
different cells and using greater than or equal to and less than o
equal to, to get a range. This will be inside an automated macro s
they won't be able to do the filter manually. My code doesn't give m
any errors but it filters everything out and doesn't show any records
Can someone tell me why my variables won't work in the code liste
below?

(THE RANGE ON SHEET2 ARE AS FOLLOWS):
("C1") = "01-May-04"
("C2") = "31-May-04"
--------------------------------------------------------------------------------
Sub myfilter()

Dim begDate As String
Dim endDate As String

begDate = Sheet2.Range("C1").Value
endDate = Sheet2.Range("C2").Value

Range("A1:H1").Select
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFilter Field:=7, Criteria1:="TRUE"
Cells.Select
Range("C1").Activate
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 57.86
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("F:F").ColumnWidth = 7.29
Columns("H:H").EntireColumn.AutoFit

Selection.AutoFilter Field:=5, Criteria1:=">=" & "begDate"
Operator:=xlAnd _
, Criteria2:="<=" & "endDate"

End Sub
 
Ad

Advertisements

M

mudraker

morry


replace

Selection.AutoFilter Field:=5, Criteria1:=">=" & "begDate"
Operator:=xlAnd _
, Criteria2:="<=" & "endDate"


with

Selection.AutoFilter Field:=5, Criteria1:=">=" & begDate
Operator:=xlAnd _
, Criteria2:="<=" & endDate


you may also need to dim your variables as dates


I would also replace

Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 57.86
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("F:F").ColumnWidth = 7.29
Columns("H:H").EntireColumn.AutoFit



with

Columns("A:H").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 57.86
Columns("F:F").ColumnWidth = 7.29


and replace

Cells.Select
Range("C1").Activate
Selection.Copy

WITH


Cells.cop
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top