Hello Bernie
well this is not coming out right. It's copying just one row but many
times. I want to copy to specific range, since the sheet where I want
to copy to has sections. I tried using the following:
Sheets("Won").Select
Rows(Application.InputBox("Enter Row")).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
but get a runtime error PasteSpecial method of Range class failed. I tesed by
using With Selection.Interior .colorindex and that work, so not sure why the
Paste doesn't. I will continue and see if I can get this to work. I really
appreciate
your help. So if you have any other suggestion please advise.
again thanks,
Juan

"Bernie Deitrick" wrote:

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