使用VBA将行复制到Excel中的另一个表 [英] Copy row to another sheet in excel using VBA

问题描述

首先，我从来没有使用VBA（或VB），所以请记住这一点。我正在尝试将一行复制到我的工作簿中的另一张表。所以每当第一行的任何更改发生在sheet1时，该行都应该​​被复制到sheet2的第2行。如果第3行在sheet1中被修改，则应在sheet2上修改行5。我想要在sheet2上的每一行之间有一个空行。我将尝试使用VBA创建一个新表，放在这个空行中。无论如何，我在Selection.Paste行中收到错误。我对VBA的知识（一小时值）非常有限，所以我可以完全按照我想要做的那样做。

  Private Sub Worksheet_Change（ByVal Target As Range）
'不要担心列标题
如果Target.Row = 1然后退出Sub 
 
'On Error GoTo ErrHandler 
 Application.EnableEvents = False'这可能是必要的？ 
 
 Sheet1.Rows（Target.Row）。选择
 Selection.Copy 
 Sheet1.Rows（Target.Row）.Copy 
 
如果Target。 Row = 2然后
 Sheet2.Rows（Target.Row）。选择
 Selection.Paste 
 Else 
 Sheet2.Select 
 Sheet2.Rows（Target.Row + Target .Row  -  1）。选择
 Selection.Paste 
 End If 
 
 ErrHandler：
 Application.EnableEvents = True'这可能是必需的？ 
 End Sub 
  

---

编辑：将 Selection.Paste 更改为 ActiveSheet.Paste ，现在正在运行。

解决方案

编辑：添加处理多区域选择

  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 
如果rw.Row> = 2然后
 rw.EntireRow.Copy Sheet2.Cells（2 +（rw.Row  -  2）* 3，1）
 End If 
 Next rw 
下一个
 End Sub 
  

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.

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.Paste to ActiveSheet.Paste, it is now working.

解决方案

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

这篇关于使用VBA将行复制到Excel中的另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！