Sorry, Juan! Typo on my part:

Cells(Rows, Count, 1).End(xlUp)(2).Select

should be

Cells(Rows.Count, 1).End(xlUp)(2).Select

HTH,
Bernie
MS Excel MVP


"Juan" wrote in message
...
Hello Bernie,
thanks but not working.
I replaced
Sheets("Sheet2").Select
Range("A9").Select
with
Sheets("Sheet2").Select
Cells(Rows, Count, 1).End(xlUp)(2).Select
But Get error" Wrong Number of arguments or invalid property assignment
Do you have any suggestions?
thanks
Juan

"Bernie Deitrick" wrote:

Baby steps....

You need to increment the rows - you are always copying to the same place.
Instead of

Sheets("Sheet2").Select
Range("A9").Select

you could use

Sheets("Sheet2").Select
Cells(Rows,Count,1).End(xlUp)(2).Select

and that won't overwrite existing data.

HTH,
Bernie
MS Excel MVP


"Juan" wrote in message
...
Hi,
Would like to do the following within a range ex
A9:Z18: If column U has an X , copy all rows which
contain X and paste to sheet 2, else if Column V has X, copy all rows and
paste
on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9.
I tried something like below:
Dim i As Long
Range("A9:Z18").Select
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "U") Like "*X*" Then
Cells(i, "U").EntireRow.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ElseIf Cells(i, "V") Like "*X*" Then
Cells(i, "V").EntireRow.Select
Selection.Copy
Sheets("Sheet3").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
etc.

But seems to only copy one row from Column U. Would appreciate any help.
Thank you,
JUAN