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
Copying specific columns conditionally to another worksheet
提问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 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.
你需要做的是For
用一个计数器从上到下读取所有单元格和工作表中的某些内容。F
是要放置内容的新工作表的行。
Try something similar to this:
尝试类似的事情:
##代码##