VBA to autofilter on current date

[kevvukeka]

Hi All,

I have a master sheet with around 3000 rows of data. In which column B contains date("Format Short date "29 - Jun - 2013" format). Now i tried to autofilter the data on current date but I am not able to do so. Can someone help me whats wrong with my below code.

[VBA]
sub test()
Dim x as date
x= date
activesheet.usedrange.autofilter field:=2,criteria1:=x
end sub
[VBA]
i also tried
[VBA]
Sub test1()
Dim x As Date, y As Date
'Dim y As Long
x = Date
x = x - 1
'x = DateSerial(Year(Now), Month(Now), Day(Now))
y = x + 2
ActiveSheet.UsedRange.AutoFilter field:=2, Criteria1:=">" & x, Operator:=xlAnd, Criteria1:="<" & y
End Sub





[/VBA]

with first code i see zero records, though i have few lines with currentdate

with second code, i see all the records though filter is applied.

Kindly help, the other part pf my code depends on this filtered data.

Thanks in advance....

[p45cal]

Awkward ain't it? Try:[VBA]Sub blah()
x = CLng(Date)
ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=">=" & x, Operator:=xlAnd, Criteria2:="<" & x + 1
End Sub
[/VBA]

[kevvukeka]

u r amazing p45cal...

It worked perfectly now...i googled so much but wasn't successful and came to vbax then..

Thanks so much again...

[kevvukeka]

Hi p45cal,

The autofilter for current date is not working again. Basically to give you an idea what I am doing is. I am consolidating multiple timesheets into one. so when I open each individual timesheet, I want to filter the data on current date, set the range to visible cells and move that data to a master sheet. Below is the part of that code which current captures entire data in each individual sheet and moves to a new sheet.

In the below code "mybook" refers to the file that is opened.
"basewks" refers to the master worksheet to where the data will be extracted.


[VBA]

dim x as date
dim mybook,basewks as worksheet
x=clng(Date)

With mybook.Worksheets("Time Sheet")
'.UsedRange.AutoFilter field:=2, Criteria1:=">=" & x,operator:=xland,criteria2:="<" & X+1
rcount = .Cells(Rows.Count, "B").End(xlUp).Row


Set sourceRange = .Range("A3:T" & rcount).specialcells(xltypevisible)
End With



Set destrange = BaseWks.Range("A" & rnum + 1)
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value



[/VBA]


I am really not sure if a range be set to filtered data with specialcells(xltypevisible) option. Kindly suggest.

Thanks

[p45cal]

try dimming x as Long, not Date.

[kevvukeka]

and will the rest of the code of work I mean setting the range to
xltypevisible?

[snb]

Use advancedfilter instead of autofilter.

[p45cal]

and will the rest of the code of work I mean setting the range to
xltypevisible?

Dunno. I don't know what's on your sheet, so when you use .usedrange I don't have the foggiest what that is.

You might need to replace xltypevisible with xlCellTypeVisible.

[SamT]

Jevi, I moved your question to a new thread: http://www.vbaexpress.com/forum/show...e-With-Headers

Then I closed this old thread