Excel VBA跨工作簿复制数据报错：PasteSpecial执行异常求助

我看了你的代码，问题主要出在几个关键的引用和范围指定上，尤其是触发错误的粘贴行，咱们一步步来修正：

核心问题分析 #

1. 错误的工作表引用：你已经通过Set rm_list = rm_master.Sheets("RMs")把目标工作表赋值给了rm_list，但粘贴时却写了rm_master.rm_list.Cells(...)——这种写法会让VBA误以为rm_list是rm_master的一个属性，而不是你定义的变量，直接导致对象找不到的错误。
2. 未指定工作表的emptyRow计算：WorksheetFunction.CountA(Range("B143:B167"))里的Range没有绑定到rm_list，默认会用当前激活的工作表，这样统计出来的行数根本不是目标文件里的真实非空行数。
3. 剪贴板依赖的复制粘贴：用Copy/PasteSpecial容易受剪贴板状态影响，比如其他程序占用剪贴板时就会报错，换成直接赋值的方式更稳定。

修正后的完整代码 #

Private Sub CommandButton1_Click()
    Dim emptyRow As Long
    Dim Exists As Boolean
    Dim answer As Integer
    Dim toomany As Integer
    Dim rm_master As Workbook
    Dim rm_list As Worksheet
    Dim vals As Variant
    
    ' 将表单值写入Sheet2临时行
    Sheet2.Range("A2").Value = IMCode.Value
    Sheet2.Range("B2").Value = IMDescription.Value
    Unload Me
    
    ' 打开主文件并绑定目标工作表
    Set rm_master = Workbooks.Open("C:\ExcelFiles\Master.xlsm")
    Set rm_list = rm_master.Sheets("RMs")
    
    ' 正确统计目标工作表B143:B167的非空行数
    emptyRow = WorksheetFunction.CountA(rm_list.Range("B143:B167"))
    
    Exists = Sheet2.Range("P2").Value
    If Exists = True Then
        answer = MsgBox("already exists.", vbOKOnly, "Already Exists")
    Else
        If emptyRow + 143 > 167 Then
            toomany = MsgBox("Too many. Please clean up or add rows.", vbOKOnly, "Too Many Codes")
        Else
            ' 直接赋值替换复制粘贴，避免剪贴板问题
            rm_list.Cells(emptyRow + 143, 2).Resize(1, 14).Value = Sheet2.Range("A2:N2").Value
        End If
    End If
    
    ' 可选：如果需要保存主文件，加上这两行
    ' rm_master.Save
    ' rm_master.Close
End Sub

关键修改说明 #

• 把rm_master.rm_list.Cells(...)改成了rm_list.Cells(...)，直接使用已定义的工作表变量
• emptyRow的计算绑定到了rm_list，确保统计的是目标工作表的行数
• 用Resize(1,14).Value = ...直接把源区域的值赋值给目标区域，彻底避免剪贴板相关的错误
• 建议加上主文件的保存和关闭逻辑（注释里的代码），避免操作后文件处于打开状态

额外注意事项 #

• 确保C:\ExcelFiles\Master.xlsm路径正确，且文件没有被其他程序锁定
• 尽量用工作表名称（比如Sheets("Sheet2")）代替Sheet2这种代号，防止后续工作表顺序调整导致引用错误

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