PDA

View Full Version : VBA to autofilter on current date



kevvukeka
06-29-2013, 01:46 AM
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.


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







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
06-29-2013, 02:56 AM
Awkward ain't it? Try:Sub blah()
x = CLng(Date)
ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=">=" & x, Operator:=xlAnd, Criteria2:="<" & x + 1
End Sub

kevvukeka
06-29-2013, 03:44 AM
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
06-30-2013, 09:47 PM
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.




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






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

Thanks

p45cal
06-30-2013, 09:53 PM
try dimming x as Long, not Date.

kevvukeka
06-30-2013, 09:58 PM
and will the rest of the code of work I mean setting the range to
xltypevisible?

snb
07-01-2013, 12:48 AM
Use advancedfilter instead of autofilter.

p45cal
07-01-2013, 01:54 AM
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
09-02-2017, 02:19 PM
Jevi, I moved your question to a new thread: http://www.vbaexpress.com/forum/showthread.php?60597-Jevi-Filter-On-Date-With-Headers

Then I closed this old thread