vba 使用VBA将行复制到excel中的另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11400369/
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 row to another sheet in excel using VBA
提问by Justin
First off, I have never used VBA (or VB for that matter), so keep that in mind. I am trying to copy a row to another sheet in my workbook. So whenever any change in row 2 will happen in sheet1, that row should be copied in row 2 on sheet2. If row 3 is modified in sheet1, row 5 should be modified on sheet2. I want there to be an empty row between each row on sheet2. I am going to try and create a new table with VBA to place inside this empty row. Anyways, I am getting an error on my Selection.Paste line. I have very limited knowledge (an hours worth) on VBA, so I could be completely off on what I am trying to do.
首先，我从未使用过 VBA（或 VB），所以请记住这一点。我正在尝试将一行复制到我的工作簿中的另一张工作表中。因此，无论何时第 2 行中的任何更改将在 sheet1 中发生，该行都应复制到 sheet2 的第 2 行中。如果在 sheet1 中修改了第 3 行，则应在 sheet2 上修改第 5 行。我希望 sheet2 上的每行之间有一个空行。我将尝试使用 VBA 创建一个新表以放置在此空行中。无论如何，我的 Selection.Paste 行出现错误。我对 VBA 的了解非常有限（一个小时），所以我可能完全不知道我想要做什么。
Private Sub Worksheet_Change(ByVal Target As Range)
'Do not worry about column headers
If Target.Row = 1 Then Exit Sub
'On Error GoTo ErrHandler
Application.EnableEvents = False 'This might be necessary?
Sheet1.Rows(Target.Row).Select
Selection.Copy
Sheet1.Rows(Target.Row).Copy
If Target.Row = 2 Then
Sheet2.Rows(Target.Row).Select
Selection.Paste
Else
Sheet2.Select
Sheet2.Rows(Target.Row + Target.Row - 1).Select
Selection.Paste
End If
ErrHandler:
Application.EnableEvents = True 'This might be necessary?
End Sub
Edit:After changing Selection.Pasteto ActiveSheet.Paste, it is now working.
编辑：更改Selection.Paste为 后ActiveSheet.Paste，它现在可以工作了。
回答by Tim Williams
EDIT: added dealing with multi-area selections
编辑：添加处理多区域选择
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range, rw As Range
For Each a In Selection.Areas
For Each rw In a.Rows
If rw.Row >= 2 Then
rw.EntireRow.Copy Sheet2.Cells(2 + (rw.Row - 2) * 3, 1)
End If
Next rw
Next a
End Sub
