transfer complete rows to new sheet

[slochin]

Hello,
I am hoping someone can assist meplease with my project.
I have two sheets with info incolumns .
In sheet one the info spreads fromA-N. In col S and T “yes” is placed if certain criteria is met.
In sheet two info spreads from A-Q.In col R if certain criteria is met a “yes” is inserted.
What I would like to do is to beable to transfer the complete row if “yes” is in the columns asabove and place them in a new sheet.

 

[slochin]

Hello mumps,
Thank you.This code transfers the selected rows from Sheet1 correctly,however does not transfer the selections from Sheet2.No error message.

 

[Fluff]

In sheet2 does the formula in col R return Yes, yes, or YES?

 

[mumps]

The macro is expecting to find the word "Yes" ( case sensitive) so if your formula is returning something like "YES" or "yes" then it won't find it. If that is the case, try:

Option Compare Text
Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Sheets("Sheet3").UsedRange.ClearContents
    For Each rng In Sheets("Sheet1").Range("S2:S" & LastRow)
        If rng = "Yes" Or rng.Offset(0, 1) = "Yes" Then
            rng.EntireRow.Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng
    LastRow = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("Sheet2").Range("R2:R" & LastRow)
        If rng = "Yes" Then
            rng.EntireRow.Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Another option would be to convert the rng value to lower case & then compare

If LCase(Rng.Value) = "yes" Then

 

[slochin]

Hello mumps,
First of allmy apologies. It appears I have been careless when creating a newsheet and somehow placed the formulas for sheet 2 in the wrongcolumn. Having corrected this ,the formula works of course asexpected.
Once again many thanks for spendingyour time to assist me and providing me with exactly what I had hopedfor.
Thanks also to Fluff for assisting

 

[mumps]

You are very welcome.