Copying specific columns conditionally to another

Question

The example i have below will copy specific rows from worksheet 1 to worksheet 2 if "YES" is found in column E. I need it to only copy specific columns of the rows, being B & C.

Fund Account Amount         Gain/Loss   As/Of? (Y/N)
1    11111    $15,000.00       -$1.51        YES
1    22222    $32,158.52       $78.14        YES
2    123123   $1.00         $0.00        NO

Code:

Sub As_Of_Analysis_Sorting()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("All Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("E" & r).Value = "YES" Then
        Rows(r).Copy Destination:=Sheets("As-Of Trades").Range("A" & lr2 + 1)
        lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
    End If

    Range("A1").Select
Next r
End Sub

Answer 1

What you need to do is to do a For with a counter that will read all the cells with something in the sheet from up to down. F is the row of the new sheet where you want to place the stuff.

Try something similar to this:

sub alfa()
    UF = Cells(Rows.Count, 1).End(xlUp).Row
    for i = 1 to uf
        if sheetname.cells(i,Columnofyes).value = "YES" then 
            sheetwheretocopy.cells(f,columnwheretocopy).value = sheetname.cells(i,columnofdata).value
            f=f+1
        end if
    next i
end sub

Answer 2

Try this:

Sub As_Of_Analysis_Sorting()
    Dim lr As Long, lr2 As Long, r As Long
    Set Sh1 = ThisWorkbook.Worksheets("All Trades")
    Set Sh2 = ThisWorkbook.Worksheets("As-Of Trades")
    Sh1.Select

    Sh2.Cells(1, 1).Value = "Account"
    Sh2.Cells(1, 2).Value = "Amount"
    lr = Sh1.Cells(Rows.Count, "A").End(xlUp).row
    x = 2
    For r = 2 To lr
        If Range("E" & r).Value = "YES" Then
            Sh2.Cells(x, 1).Value = Sh1.Cells(r, 2).Value
            Sh2.Cells(x, 2).Value = Sh1.Cells(r, 3).Value
            x = x + 1
        End If
    Next r
    Sh2.Select
End Sub

---

New request:

Sub As_Of_Analysis_Sorting()
    Dim lr As Long, lr2 As Long, r As Long
    Set Sh1 = ThisWorkbook.Worksheets("All Trades")
    Set Sh2 = ThisWorkbook.Worksheets("As-Of Trades")
    Sh1.Select

    Sh2.Cells(1, 1).Value = "Account"
    Sh2.Cells(1, 2).Value = "Amount"
    lr = Sh1.Cells(Rows.Count, "A").End(xlUp).row
    x = 2
    For r = 2 To 30
        If Range("E" & r).Value = "YES" Then
            Sh2.Cells(x, 1).Value = Sh1.Cells(r, 2).Value
            Sh2.Cells(x, 2).Value = Sh1.Cells(r, 3).Value
            x = x + 1
        End If
    Next r
    x = 35
    For r = 31 To lr
        If Range("E" & r).Value = "YES" Then
            Sh2.Cells(x, 1).Value = Sh1.Cells(r, 2).Value
            Sh2.Cells(x, 2).Value = Sh1.Cells(r, 3).Value
            x = x + 1
        End If
    Next r
    Sh2.Select
End Sub