Excel VBA：PasteValues第二次循环触发Run-time error 1004原因排查

让我来帮你拆解这个问题——我遇到过不少类似的VBA剪贴板和活动引用的坑，你的情况其实是典型的剪贴板丢失+不稳定引用导致的问题。

为什么会触发Run-time Error 1004？ #

这个错误的核心原因有两个：

• 剪贴板内容被清空：你的代码先复制了主工作簿的Summary区域，然后执行打开目标工作簿的操作——Excel在打开外部文件时，经常会悄悄清空剪贴板，等你到粘贴那一步时，已经没有可粘贴的内容了，自然触发1004错误。
• 依赖ActiveWorkbook/Select这类不稳定操作：代码里到处用ActiveWorkbook和Select，这些操作完全依赖当前Excel的活动状态，只要工作簿切换、焦点变一下，引用就会混乱，很容易出问题。

为什么第一次循环正常，第二次失败？ #

• 第一次循环：目标文件还不存在，你执行的是Workbooks.Add创建新工作簿——这个操作不会清空剪贴板，而且你复制的Summary内容还在。同时新创建的工作簿就是当前活动工作簿，Worksheets(tabName)能精准指向新工作表，粘贴自然顺利完成。
• 第二次循环：目标文件已经存在，你执行Workbooks.Open打开它——这个过程中Excel大概率清空了之前复制的内容。等你走到粘贴那一行时，剪贴板是空的，自然就报错了。另外，第一次循环后如果你没关闭目标工作簿，第二次打开时虽然不会直接报错，但会进一步打乱活动工作簿的状态，让引用问题更严重。

修复后的代码示例 #

要解决这个问题，我们需要彻底抛弃不稳定的ActiveWorkbook/Select，改用明确的引用，同时调整操作顺序，避免剪贴板被清空：

Private Sub PasteValue()
    Dim tabName As String
    Dim filePath As String
    Dim mainWB As Workbook
    Dim targetWB As Workbook
    Dim targetWS As Worksheet
    
    ' 明确绑定包含当前代码的主工作簿，再也不用担心活动状态变化
    Set mainWB = ThisWorkbook
    tabName = mainWB.Sheets("Main").Range("D5").Value
    filePath = "C:\Users\ME\Desktop\My Work\test\All Test Results Paste Values.xlsx"
    
    ' 先检查目标工作簿是否已经打开，避免重复打开
    On Error Resume Next
    Set targetWB = Workbooks(Dir(filePath))
    On Error GoTo 0
    
    ' 如果没打开，再判断文件是否存在，决定新建还是打开
    If targetWB Is Nothing Then
        If Dir(filePath) = "" Then
            Set targetWB = Workbooks.Add
        Else
            Set targetWB = Workbooks.Open(fileName:=filePath)
        End If
    End If
    
    ' 处理目标工作表：存在就用，不存在就新建
    On Error Resume Next
    Set targetWS = targetWB.Sheets(tabName)
    On Error GoTo 0
    
    If targetWS Is Nothing Then
        Set targetWS = targetWB.Sheets.Add(After:=targetWB.Sheets(targetWB.Sheets.Count))
        targetWS.Name = tabName
    End If
    
    ' 先复制内容，然后立即粘贴——把复制操作放在最后，避免被打开文件清空剪贴板
    mainWB.Sheets("Main").Range("Summary").Copy
    With targetWS.Range("A1")
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    Application.CutCopyMode = False ' 手动清空剪贴板，释放Excel资源
    
    ' 保存目标工作簿
    If Dir(filePath) = "" Then
        targetWB.SaveAs fileName:=filePath
    Else
        targetWB.Save
    End If
    
    ' 可选：如果不需要保留目标工作簿打开，可以取消下面这行注释关闭它
    ' targetWB.Close SaveChanges:=False
End Sub

几个关键优化点 #

• 用ThisWorkbook绑定主工作簿，彻底摆脱对活动状态的依赖
• 先处理好目标工作簿和工作表，再执行复制操作，避免剪贴板被打开文件的操作清空
• 检查目标工作簿是否已打开，防止重复打开引发的异常
• 复制后立即粘贴，最大程度降低剪贴板内容丢失的概率
• 用完剪贴板后手动释放资源，避免Excel卡顿

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