VBA报错Error 1004：Worksheet类的Paste方法执行失败求助

你遇到的Error 1004（Worksheet类的Paste方法执行失败），核心问题是依赖ActiveSheet和Activate的操作逻辑不稳定——新增工作表后，剪贴板内容可能丢失，或者激活状态未及时切换导致粘贴目标错误。直接用对象引用替代激活操作，同时改用更可靠的赋值方式替代Copy/Paste，可以彻底解决这个问题。

修复后的代码 #

Public Sub ImportTextFile()
    Dim TextFile As Workbook
    Dim OpenFiles() As Variant
    Dim i As Integer
    Dim targetWB As Workbook
    Dim newWS As Worksheet
    Dim sourceRange As Range
    
    ' 初始化目标工作簿（即当前运行代码的工作簿）
    Set targetWB = ThisWorkbook
    
    OpenFiles = Application.GetOpenFilename(Title:="选择要导入的文件", MultiSelect:=True)
    
    ' 取消选择文件时直接退出
    If VarType(OpenFiles) = vbBoolean Then Exit Sub
    
    Application.ScreenUpdating = False
    
    For i = LBound(OpenFiles) To UBound(OpenFiles)
        On Error Resume Next
        Set TextFile = Workbooks.Open(OpenFiles(i))
        On Error GoTo 0
        
        ' 跳过打开失败的文件
        If TextFile Is Nothing Then
            MsgBox "无法打开文件：" & OpenFiles(i)
            GoTo NextFile
        End If
        
        ' 获取源数据区域
        Set sourceRange = TextFile.Sheets(1).Range("A1").CurrentRegion
        
        ' 在目标工作簿新增工作表
        Set newWS = targetWB.Worksheets.Add(After:=targetWB.Worksheets(targetWB.Worksheets.Count))
        
        ' 直接赋值数据，替代Copy/Paste，更稳定
        sourceRange.Copy Destination:=newWS.Range("A1")
        
        ' 处理工作表名：去除非法字符，避免重命名失败
        Dim wsName As String
        wsName = Left(TextFile.Name, InStrRev(TextFile.Name, ".") - 1) ' 去掉扩展名
        wsName = Replace(Replace(Replace(Replace(wsName, "/", "-"), "\", "-"), ":", "-"), "?", "-")
        wsName = Replace(Replace(Replace(wsName, "*", "-"), "[", "-"), "]", "-")
        newWS.Name = wsName
        
        ' 关闭源工作簿
        TextFile.Close SaveChanges:=False
        Set TextFile = Nothing
        
NextFile:
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "数据导入完成！"
End Sub

关键修改说明 #

• 放弃激活操作：直接用targetWB（当前代码所在工作簿）和newWS（新增的工作表）对象引用，避免Activate和ActiveSheet带来的状态不稳定问题。
• 改用Copy Destination：一步完成复制粘贴，无需单独调用Paste，避免剪贴板内容丢失或粘贴目标错误。
• 处理工作表名非法字符：源文件名中的/\:*?[]等字符不能用于工作表名，提前替换为-，防止重命名失败。
• 增加错误处理：跳过无法打开的文件，避免程序崩溃。

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