vba 将一行从一张纸复制粘贴到另一张纸上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12147629/
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 paste a row from one sheet to another
提问by user1628425
I am trying to copy rows from Sheet1 which meet a crieteria and post the whole row at the end of the current data. I am able to copy the row but it is not pasting it. Help will be appreciated. Here is my code I have written:
我正在尝试从 Sheet1 复制满足条件的行，并将整行发布到当前数据的末尾。我能够复制该行，但它没有粘贴它。帮助将不胜感激。这是我写的代码：
Sub Button1_Click()
Dim i As Integer
'Range("H2:O65536").ClearContents
Sheets("Sheet1").Select
LastRowColA = Range("A65536").End(xlUp).Row
For i = 2 To LastRowColA
If Cells(i, 6) = "No" Then
Rows(i).Select
Rows(i).Copy
Sheets("Sheet2").Select
Dim LastRow As Long
Dim StartRow As Long
Dim Col As Long
Dim Row As Long
StartRow = 2
Col = 1
LastRow = findLastRow(1)
For Row = StartRow To LastRow
Rows(LastRow).Select
ActiveSheet.Paste
Next Row
Else
'do nothing
End If
Next i
End Sub
Function findLastRow(ByVal Col As Integer) As Long
'Find the last row with data in a given column
findLastRow = Cells(Rows.Count, Col).End(xlUp).Row
End Function
回答by The_Barman
here we go: a tad shorter, but should do the job...
我们走了：有点短，但应该可以完成工作......
Sub Button1_Click()
Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")
For i = 2 To ws1.Range("A65536").End(xlUp).Row
If ws1.Cells(i, 6) = "No" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 6).End(xlUp).Row + 1)
Next i
End Sub
回答by Scott Holtzman
To add a bit more help, why spend all that (processing) time looping through a potentially large row set when you can just filter and copy all your data at once?
为了增加一点帮助，当您可以一次过滤和复制所有数据时，为什么要花费所有（处理）时间循环遍历一个潜在的大行集？
See code below. You may need to tweak it a bit to match your data set.
请参阅下面的代码。您可能需要稍微调整一下以匹配您的数据集。
Sub Button1_Click()
Dim ws1 as Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 as Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")
With ws1
.UsedRange.AutoFilter 6, "No"
'-> assumes data starts in column A, if not adjust the 6
Intersect(.UsedRange,.UsedRange(Offset(1)).SpecialCells(xlCellTypeVisible).Copy
' -> assumes No's are there, if they may not exist, will need to error trap.
End With
With ws2
.Rows(.Cells(ws2.Rows.Count, 6).End(xlUp).Row + 1).PasteSpecial xlPasteValues
End With
ws1.AutoFilterMode = False
End Sub
回答by Manimaran A
// Just use it.
// 只是使用它。
Sheet2.Select (Sheet1.Rows(index).Copy)
Sheet2.Paste (Rows(index))
If you want to copy, paste two or more rows then use the for loop.
如果要复制、粘贴两行或更多行，然后使用 for 循环。
