VBA code to set autofilter based on start date and end date in seperate cells of the worksheet.

[MrsFixIt]

I have a table with 23k rows of data and there is a column with dates from 2004 to current date. Each month I need to filter and copy the current month rows into a new worksheet. I want the macro to filter a start date in cell K1 and end date in cell K2.
I cannot the after/greater than K1 and before/less than K2 to work.

This is what I have so far.

Dim day1 As String
Dim day2 As String
day1 = Sheets("Load").Range("K1").Value 'start date
day2 = Sheets("Load").Range("K2").Value 'end date
'
ActiveSheet.Range("$B$2:$H$23751").AutoFilter Field:=5, Criteria1:= _
">" & day1, Operator:=xlAnd, Criteria2:="<" & day2

 

[mrshl9898]

Try this instead:

Dim day1 As Date
Dim day2 As Date
day1 = Sheets("Load").Range("K1").Value 'start date
day2 = Sheets("Load").Range("K2").Value 'end date
'
ActiveSheet.Range[COLOR=#333333]("$B$2:$H$23751").AutoFilter Field:=5[/COLOR], Criteria1:= _
">" & CDbl(day1), Operator:=xlAnd, Criteria2:="<" & CDbl(day2)

 

[MrsFixIt]

Bless You! It works perfectly! Now all I need to do is copy the results into a new worksheet in the same file. Do you have anything like that in your cookbook?