Excel VBA命令按钮复制行到指定工作表不覆盖数据问题

原有代码报错/覆盖数据的核心原因 #

• InputBox类型参数错误：选择单元格/区域的Application.InputBox对应的Type参数固定为8，第一段代码写的Type:=64对应数组类型输入，必然触发类型不匹配报错。
• 粘贴位置逻辑错误：原有代码要求手动选择粘贴区域，没有自动定位存量数据末尾的逻辑，只要选到已有数据的位置就会覆盖内容，也无法保证粘贴到结构化表格范围内，会导致后续排序功能失效。
• 冗余的激活操作：代码中反复使用Activate激活工作表、单元格，容易触发跨工作表焦点错误，也会导致运行时屏幕跳变。

可直接使用的实现代码 #

Private Sub CommandButton3_Click()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim rngToCopy As Range
    Dim targetPasteRow As Long
    Dim targetTable As ListObject
    
    ' 绑定当前按钮所在工作表为复制源表，可根据实际需求修改
    Set wsSource = Me
    ' 错误捕获：处理用户点取消、目标表不存在等异常
    On Error Resume Next
    Set wsTarget = ThisWorkbook.Worksheets("6.2022 Basis")
    On Error GoTo 0
    If wsTarget Is Nothing Then
        MsgBox "未找到名为""6.2022 Basis""的工作表，请检查", vbExclamation
        Exit Sub
    End If
    
    ' 选择待复制的行区域，支持手动选区域/输入行号
    On Error Resume Next
    Set rngToCopy = Application.InputBox( _
        Prompt:="请选择要复制的行（可直接输入行号，例如输入2:2代表第2行）", _
        Title:="选择待复制内容", _
        Type:=8)
    On Error GoTo 0
    If rngToCopy Is Nothing Then
        ' 用户点击取消直接退出
        Exit Sub
    End If
    
    ' 定位粘贴起始行：优先适配结构化表格，保证排序功能正常
    Set targetTable = Nothing
    On Error Resume Next
    ' 假设目标表的结构化表格从A1开始，可根据实际表格位置调整
    Set targetTable = wsTarget.ListObjects(1)
    On Error GoTo 0
    
    If Not targetTable Is Nothing Then
        ' 存在结构化表格，追加到表格末尾，自动扩展表格范围
        targetPasteRow = targetTable.ListRows.Count + targetTable.HeaderRowRange.Row + 1
    Else
        ' 普通区域，定位到已使用区域的下一行
        targetPasteRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1
    End If
    
    ' 带链接粘贴，不覆盖原有数据
    rngToCopy.Copy
    wsTarget.Cells(targetPasteRow, 1).PasteSpecial Link:=True
    Application.CutCopyMode = False
    
    MsgBox "内容已成功追加到目标工作表末尾", vbInformation
End Sub

使用说明 #

• 代码默认将按钮所在工作表作为复制源表，如果你的复制源是固定名称的工作表，可把Set wsSource = Me修改为Set wsSource = ThisWorkbook.Worksheets("你的源表名")。
• 如果目标工作表的结构化表格不是从A1单元格开始、或存在多个结构化表格，可把Set targetTable = wsTarget.ListObjects(1)修改为Set targetTable = wsTarget.ListObjects("你的表格名称")，指定要追加的表格对象。
• 粘贴时默认从目标表A列开始粘贴，如果需要偏移列，可修改wsTarget.Cells(targetPasteRow, 1)中的第二个参数，比如改成3就是从C列开始粘贴。

内容的提问来源于stack exchange，提问作者jmowalker