Copy all Rows If Column Value X within a Range

[Juan]

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

 

[Bernie Deitrick]

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]

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]

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]

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]

Juan,

We were forgetting to re-select the sheet with the data after the
pastespecial.

Try it this way:

Sub Test()
Dim i As Long
Dim mySht As Worksheet
Set mySht = ActiveSheet
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If mySht.Cells(i, "U") Like "*X*" Then
mySht.Cells(i, "U").EntireRow.Copy
Sheets("Sheet2").Select
Cells(Rows.Count, 1).End(xlUp)(2).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
Next i
End Sub


HTH,
Bernie
MS Excel MVP

 

[Juan]

Hello Bernie,
thanks alot this works. Just one question, let's say if my sheet where I'm
copying has sections with data example America uses range (A8:Z19), Europe
(A24:Z34) etc, example
A8 W2009
a9:a18(Is there a way to copy those rows here)?
A19 Total
the Cells(Rows.Count, 1).End(xlUp)(2).Select puts rows at end.. Please
advise if you have an idea.
Really appreciate the time you have put in helping me out.
Thanks,
Juan