Hi Bob,

Your code works perfect !
I am working on transfering lotus.123 macro to Excel
macros right now,and I am a novice in using VBA.Your
code is very useful and I am sure I will use it a lot.
Again thanks for your help.

Meme

-----Original Message-----
Meme,
Those 2 lines should be a single line, The NG wrapped
around. Try this
slightly amended version

Sub CopyData()
Dim iLastRow As Long
With Worksheets("A")
iLastRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Columns("E:E").EntireColumn.Insert
.Range("E2").FormulaR1C1 = "=AND(OR(RC[-3]
=""user01""," & _
"RC[-3]
=""user03""," & _
"RC[-3]
=""user04""," & _
"RC[-3]
=""user05"")," & _
"AND(RC[-2]=DATE
(2004,4,1)," & _
"RC[-2]<=DATE
(2004,4,3)))"
.Range("E2").AutoFill Destination:=.Range("E2:E"
& iLastRow), _
Type:=xlFillDefault
.Columns("E:E").AutoFilter Field:=1,
Criteria1:="<FALSE"
.Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("B").Range("A1")
.Columns("E:E").EntireColumn.Delete
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Meme" wrote in
message
...
Hi Bob,
Thank you so much for the macro you gave to me. but
some
how this following code has a syntax error when I ran
the
macro. Please help me check this code:

.Range("E2").AutoFill Destination:=.Range
("E2:E"
& iLastRow),Type:=xlFillDefault

Thank you in advance !
Meme

-----Original Message-----
Hi Meme,

Here is a macro

Sub CopyData()
Dim iLastRow As Long
With Worksheets("A")
iLastRow = .Cells(Rows.Count, "C").End
(xlUp).Row
.Columns("E:E").EntireColumn.Insert
.Range("E2").FormulaR1C1 = "=AND(OR(RC[-3]
=""user01""," & _
"RC[-3]
=""user03""," & _
"RC[-3]
=""user04""," & _
"RC[-3]
=""user05"")," & _
"AND(RC[-2]
=DATE
(2004,4,1)," & _
"RC[-2]
<=DATE
(2004,4,3)))"
.Range("E2").AutoFill Destination:=.Range
("E2:E"
& iLastRow),
Type:=xlFillDefault
.Columns("E:E").AutoFilter Field:=1,
Criteria1:="<FALSE"
.Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("B").Range("A1")
.Columns("E:E").EntireColumn.Delete
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

wrote in message
...
Please help!

I have a sheet(A) from colum A to colum D with
field
name like this:
plan user date total

There are about 500 records in this sheet(A). now I
want
to select records with date btw 04/01/04 to 04/03/04
and
user name a
user01, user05, user03, usero4.
Is there anyway that I can program a VBA code to
aumotically select those records and copy-paste
them to
sheet B?
Your help is greatly appreciated !!!!!

Meme


.



.