vba 使用宏在excel中将行从一个工作表复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11568091/
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 rows from one worksheet to another in excel using macro
提问by duper
I have an excel worksheet with whole bunch of rows and several columns in it. The 1st column contains the manufacturer's name, the 2nd column contains the product codes for all the products, the 3rd column contains the description and etc. What I want to do is to copy the rows that corresponds to certain product codes. For example:
我有一个 excel 工作表，里面有一大堆行和几列。第一列包含制造商名称，第二列包含所有产品的产品代码，第三列包含描述等。我想要做的是复制与某些产品代码对应的行。例如：
**Manufacturer Product code Description**
abc B010 blah blah
dgh A012
hgy X010
eut B013
uru B014
eut B015
asd G012
sof B016
uet B016
etc
Is there a way to copy the rows that has the product codes in between B010 - B016? There might be double/matching product codes too, and it is totally fine to copy them too.
有没有办法复制产品代码在 B010 - B016 之间的行？也可能有双重/匹配的产品代码，复制它们也完全没问题。
Makes sense?
说得通？
Sorry, i have no vba code to put in here yet.
抱歉，我还没有可以放入这里的 vba 代码。
Thanks in advance.
提前致谢。
采纳答案by LittleBobbyTables - Au Revtheitroad
This should do the trick; it copies the A:C range cells for any B cell values that are between B010 and B016 to the next available row in Sheet2.
这应该可以解决问题；它将 B010 和 B016 之间的任何 B 单元格值的 A:C 范围单元格复制到 Sheet2 中的下一个可用行。
Private Sub CopyRows()
Dim lastrow As Long
Dim r1 As Long, r2 As Long
' Get the last row in the worksheet
lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
r2 = 1
For r1 = 1 To lastrow
' If the last three characters of the B cell are numeric...
If IsNumeric(Right(Sheet1.Range("$B$" & r1).Value, 3)) Then
' If the first character of the B cell is "B", and the last three
' characters are between 10 and 16 ...
If Left(Sheet1.Range("$B$" & r1).Value, 1) = "B" And _
CLng(Right(Sheet1.Range("$B$" & r1).Value, 3)) >= 10 And _
CLng(Right(Sheet1.Range("$B$" & r1).Value, 3)) <= 16 Then
' ... copy the A-C range for the row to the next available row
' in Sheet2
Sheet2.Range("$A$" & r2, "$C$" & r2).Value = _
Sheet1.Range("$A$" & r1, "$C$" & r1).Value
r2 = r2 + 1
End If
End If
Next
End Sub
