Excel VBA跨表复制行到表格不覆盖数据及438错误排查

错误原因 #

报错核心是ListObject（Excel内置表格）的引用写法错误：Worksheet对象没有直接以表格名作为属性的访问规则，你写的ThisWorkbook.Worksheets("6.2022 Basis").Basis_Table属于非法调用，VBA找不到对应属性就会抛出438错误。
另外原代码没有适配工作表锁定权限的场景，直接在受保护工作表上新增行会触发权限报错，同时多余的工作表激活操作会造成界面闪烁。

修复要点 #

• 改用ListObjects集合按名称/索引获取目标表格对象，这是VBA访问内置表格的标准写法
• 新增工作表保护逻辑：操作前临时解除工作表保护，操作完成后自动恢复，适配多用户使用的锁定权限场景
• 增加选择范围校验：如果用户选的列数和目标表列数不匹配直接提示退出，避免列错位
• 移除不必要的Activate激活操作，减少界面卡顿闪烁
• 通过ListRows.Add新增的行会自动纳入表格结构，原有绑定在表格上的排序宏可以直接识别这些行，不需要额外配置
• 移除手动选择粘贴位置的逻辑，所有内容自动追加到表格末尾，从根源避免覆盖原有数据

修正后完整代码 #

Private Sub CommandButton3_Click()
    Dim rngToCopy As Range
    Dim wsBasis As Worksheet
    Dim DataTable As ListObject
    Dim DataRow As ListRow
    Dim r As Long, c As Long
    Dim protectStatus As Boolean
    
    ' 选择Updates表中待复制范围
    On Error Resume Next
    Set rngToCopy = Application.InputBox("Select range in Updates", Type:=8)
    On Error GoTo 0
    If rngToCopy Is Nothing Then Exit Sub
    
    ' 绑定目标工作表和内置表格对象
    Set wsBasis = ThisWorkbook.Worksheets("6.2022 Basis")
    ' 按表名获取对象，若表名匹配错误可改为Set DataTable = wsBasis.ListObjects(1)取工作表第一个表
    Set DataTable = wsBasis.ListObjects("Basis_Table")
    
    ' 校验复制列数和表格列数是否匹配
    If rngToCopy.Columns.Count > DataTable.ListColumns.Count Then
        MsgBox "选择的列数超过目标表格总列数，请重新选择", vbExclamation
        Exit Sub
    End If
    
    ' 记录并临时解除工作表保护
    If wsBasis.ProtectContents Then
        protectStatus = True
        ' 替换为实际设置的工作表保护密码，无密码则删除Password参数
        wsBasis.Unprotect Password:="你的工作表保护密码"
    End If
    
    ' 逐行新增表格行并写入跨表链接公式
    For r = 1 To rngToCopy.Rows.Count
        Set DataRow = DataTable.ListRows.Add
        For c = 1 To rngToCopy.Columns.Count
            If rngToCopy.Cells(r, c) <> "" Then
                DataRow.Range.Cells(1, c).Formula = "=" & rngToCopy.Cells(r, c).Address(External:=True)
            End If
        Next
    Next
    
    ' 恢复工作表保护
    If protectStatus Then
        wsBasis.Protect Password:="你的工作表保护密码", UserInterfaceOnly:=True
    End If
End Sub

使用说明 #

• 如果目标表名不是Basis_Table，把代码里ListObjects("Basis_Table")的字符串改成实际的表格名称即可
• 代码里的工作表保护密码替换成实际设置的密码，如果没有设置保护密码，直接删掉Password:="你的工作表保护密码"这段参数
• 写入公式默认和选择范围的列顺序一一对应，如果需要固定列映射关系，可以自行调整循环中c的对应规则

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