Copy rows automatically
 

I'm using the following code to copy rows automatically based on certain
criteria within the code. How can I set it up so that one of my selected
criterias is always using less than the current date?

This is the code line I'm looking to change:
rng.AutoFilter Field:=15, Criteria1:="=01/01/2000", Operator:=xlAnd,
Criteria2:="<=07/28/2008" '

Right now I have criteria2 set to <=07/28/2008 but I want this to be less
than todays date. I tried <Today() but that didn't work. How would this work?

Copy rows automatically
 

Not a direct answer to your query here. An alternative is the same simple
non-array formulas play (see response to your earlier query) to automatically
extract the results in another sheet

Assume source data in Sheet1's cols A to O, data from row2 down
Col O is the key col, with real dates

In Sheet1,
Put in P2:
=IF(AND(O2=--"1-Jan-2000",O2<TODAY()),ROW(),"")
Copy P2 down to cover the max expected extent of data in col O, eg down to
P2000? Leave P1 blank. This is the criteria col

Then in another sheet,
Put in A2:
=IF(ROWS($1:1)COUNT(Sheet1!$P:$P),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!$P:$P,ROWS($1:1))))
Copy A2 across to O2, fill down to cover the same extent as done in Sheet1's
col P. Cols A to G will auto-return the required results from Sheet1, with
all lines neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Secret Squirrel" wrote:
I'm using the following code to copy rows automatically based on certain
criteria within the code. How can I set it up so that one of my selected
criterias is always using less than the current date?

This is the code line I'm looking to change:
rng.AutoFilter Field:=15, Criteria1:="=01/01/2000", Operator:=xlAnd,
Criteria2:="<=07/28/2008" '

Right now I have criteria2 set to <=07/28/2008 but I want this to be less
than todays date. I tried <Today() but that didn't work. How would this work?

Copy rows automatically
 

What about like this...

rng.AutoFilter Field:=15, Criteria1:="=01/01/2000", _
Operator:=xlAnd, Criteria2:="<=" & Date

Rick


"Secret Squirrel" wrote in
message ...
I'm using the following code to copy rows automatically based on certain
criteria within the code. How can I set it up so that one of my selected
criterias is always using less than the current date?

This is the code line I'm looking to change:
rng.AutoFilter Field:=15, Criteria1:="=01/01/2000", Operator:=xlAnd,
Criteria2:="<=07/28/2008" '

Right now I have criteria2 set to <=07/28/2008 but I want this to be less
than todays date. I tried <Today() but that didn't work. How would this
work?