使用 VBA 将数据从一张纸复制到另一张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6305504/
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
Copy data from one sheet to another using VBA
提问by Mike
in excel 2007 trying to loop through (un-fixed length) column (say C) and where row value matches (say "High"), then transfer value of cells Dx and Bx to sheet "transfer" in new row, where x is the row# where the matches are found. Assume "transfer" exists.
在 excel 2007 中，尝试遍历（非固定长度）列（例如 C）和行值匹配的位置（例如“高”），然后将单元格 Dx 和 Bx 的值转移到新行中的表“转移”，其中 x 是找到匹配项的行#。假设存在“转移”。
So far I've got this:
到目前为止，我有这个：
Public Sub CopyRows()
Sheets("Sheet1").Select
'Find the last row of data
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through each row
For x = 1 To FinalRow
'Decide if to copy based on column C
ThisValue = Cells(x, 3).Value
If ThisValue = "High" Then
Cells(x, 1).Resize(1, 33).Copy
Sheets("Transfer").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
Next x
End Sub
回答by Juliusz
I would not go that way. I believe setting up a filter first and copy only visible rows would work faster and be easier to code.
我不会走那条路。我相信首先设置过滤器并仅复制可见行会更快地工作并且更容易编码。
回答by GSerg
In fact, that's best done with advanced filter.
事实上，最好使用高级过滤器来完成。
Create two ranges -- one for the condition (header and a cell):
创建两个范围——一个用于条件（标题和单元格）：
*C column header* | ------------------- High |
And one for wanted data (header only):
还有一个用于想要的数据（仅标题）：
*B column header* | *D column header* -------------------------------------
Then use
然后使用
range("A:D").advancedfilter xlFilterCopy, range("criteria range"), range("copy range")
Obviously, this is easier done with Excel interface (Data - Advanced Filter).
显然，使用 Excel 界面（数据 - 高级过滤器）更容易做到这一点。
