vba 有条件地将特定列复制到另一个工作表

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/26986063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-12 05:17:03  来源:igfitidea点击:

Copying specific columns conditionally to another worksheet

excelvbacopy-pasteworksheet

提问by Bradley Jones

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.

如果在 E 列中找到“是”,我下面的示例会将工作表 1 中的特定行复制到工作表 2。我只需要它复制行的特定列,即 B 和 C。

Fund Account Amount         Gain/Loss   As/Of? (Y/N)
1    11111    ,000.00       -.51        YES
1    22222    ,158.52       .14        YES
2    123123   .00         
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
.00 NO

Code:

代码:

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

回答by SkyMaster

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 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


New request:

新请求:

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

回答by Chakal

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

你需要做的是For用一个计数器从上到下读取所有单元格和工作表中的某些内容。F是要放置内容的新工作表的行。

Try something similar to this:

尝试类似的事情:

##代码##