有条件地将特定的列复制到另一个工作表 [英] Copying specific columns conditionally to another worksheet

问题描述

如果在列E中找到YES，我下面的示例会将工作表1中的特定行复制到工作表2.
我需要它仅复制行的特定列，即B& C。

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

代码：

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

推荐答案

尝试这样： / p>

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

这篇关于有条件地将特定的列复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！