vba 将数据复制到新工作表（选项卡）中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27514917/
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 into new sheet (tab)
提问by Charanpreet Singh Atli
Sheet1 Sheet2 (output)
A B C A B C
1 Name1 100 May 1 Name1 100 May
2 Name2 200 June 2 Name2 200 June
3 Name3 Oct 3 Name3 Oct
4 Name4 300 4 Name4 300
5 Name5
I want to read the values in Columns B and C. IF any value exist out of 2 then pull that row into new sheet or tab of the same Excel workbook. If columns B and C are blank then skip that row and move onto next row.
我想读取 B 列和 C 列中的值。如果 2 列中存在任何值，则将该行拉入同一 Excel 工作簿的新工作表或选项卡中。如果列 B 和 C 为空，则跳过该行并移至下一行。
回答by NEOmen
Below is the code with explanations in the comments
下面是注释中带有解释的代码
Tested
已测试
Sub checkNcopy()
'Checking the last populated row
lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
counter = 2
'Copying the variable Names
for i=1 to 3
Worksheets("Sheet2").Cells(1, i) = Worksheets("Sheet1").Cells(1, i)
Next
For i = 2 To lastRow
'Checking if one of the two columns B and C are populated or not
If Not IsEmpty(Worksheets("Sheet1").Cells(i, 2)) Or Not IsEmpty(Worksheets("Sheet1").Cells(i, 3)) Then
'If one of the two variables are populated then copying the data from sheet1 to sheet2
for j=1 to 3
Worksheets("Sheet2").Cells(counter, j) = Worksheets("Sheet1").Cells(i, j)
Next
counter = counter + 1
End If
Next
End Sub
回答by Qbik
You can use .CurrentRegion()on active range to obtain range which correspond to whole table, and .Copy()method :
您可以使用.CurrentRegion()活动范围来获取对应于整个表格的范围，以及.Copy()方法：
Sub CopyData()
Worksheets("Sheet1").Range("A1").Activate
ActiveCell.CurrentRegion.AutoFilter Field:=2, Criteria1:="<>"
ActiveCell.CurrentRegion.AutoFilter Field:=3, Criteria1:="<>"
ActiveCell.CurrentRegion.Copy Worksheets("Sheet2").Range("A1")
End Sub
Slightly more proper, but less readable version is :
稍微更合适但可读性较差的版本是：
Sub CopyDataxxx()
Dim TargetRange As Range
Worksheets("Sheet1").Activate
Range("A1").Activate
Set TargetRange = ActiveCell.CurrentRegion
With TargetRange
.AutoFilter Field:=3, Criteria1:="<>"
.Copy Worksheets("Sheet2").Range("A1")
.AutoFilter 'switches off AutoFilter
End With
End Sub
