为什么有时PasteSpecial方法会引发错误1004? [英] Why does PasteSpecial method sometimes throw error 1004?

问题描述

我想将几个工作表的数据复制到一个工作表中，并复制表中除第一行以外的所有内容.

I want to copy the data of several worksheets into one worksheet and copy everything from the table except the first row.

PasteSpecial有时会失败

PasteSpecial fails sometimes with

错误1004范围类的粘贴特殊方法失败"

Error 1004 "pastespecial method of range class failed"

我可以点击调试"然后重新开始，代码将继续复制.当我在整个过程中多次执行此操作时，我就结束了.

I can click "debug" and then start again and the code continues copying. When I do this several time through the process I get to the end.

我尝试了其他粘贴模式，例如 .paste ，并添加了 .activate 和 .select 语句.

I tried other paste modes like .paste and added .activate and .select statements.

您知道为什么会发生这种行为以及如何解决此问题吗?

Any idea why this behavior occurs and how it could be fixed?

Option Explicit

Sub RunOnAllFilesInFolder()

    Dim folderName As String, eApp As Excel.Application, fileName As String
    Dim wb As Workbook, ws As Worksheet, currWs As Worksheet, currWb As Workbook
    Dim sht As Worksheet
    Dim fDialog As Object: Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    Dim LastRowWb As Integer, LastRow As Integer
    Dim eof As Integer
    
    Set currWb = ActiveWorkbook: Set currWs = ActiveSheet
    
    Set ws = ThisWorkbook.Worksheets("Artikelliste")
    
    fDialog.Title = "Select a folder"
    fDialog.InitialFileName = currWb.Path
    If fDialog.Show = -1 Then
        folderName = fDialog.SelectedItems(1)
    End If
    
    Set eApp = New Excel.Application:  eApp.Visible = False
    
    fileName = Dir(folderName & "\*.*")
    
    LastRow = 2
    
    Do While fileName <> ""
        'Update status bar to indicate progress
        Application.StatusBar = "Processing " & folderName & "\" & fileName
 
        Set wb = eApp.Workbooks.Open(folderName & "\" & fileName)
        Set sht = wb.Worksheets("Tabelle1")
        
        LastRowWb = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
        
        sht.Activate
        sht.Range("A2" & ":" & "AM" & LastRowWb).Copy
        
        ws.Range("A" & LastRow).PasteSpecial Paste:=xlPasteFormats
        ws.Cells(LastRow, 15).Value = fileName

        ThisWorkbook.Save
        LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1

        eApp.CutCopyMode = False
        
        wb.Close SaveChanges:=False 
        Debug.Print "Processed " & folderName & "\" & fileName
        fileName = Dir()
    Loop
    
    eApp.Quit
    Set eApp = Nothing
    Application.DisplayAlerts = True
    
    Application.StatusBar = ""
    MsgBox "Completed executing macro on all workbooks"
    
End Sub

推荐答案

您知道为什么会发生这种奇怪的行为以及如何解决该问题吗?

Any idea why this strange behavior occurs and how it even could be fixed?

1. Excel具有清除剪贴板的怪异习惯，因此建议在复制和粘贴操作之间不要做任何其他事情

1. Excel has an uncanny habit of clearing the clipboard and hence it is advisable not to do anything else between copy and paste operations

您需要给Excel时间将数据放置在剪贴板上.尤其是当您尝试循环执行复制粘贴操作时.

You need to give Excel time to place data on the clipboard. Especially when you are trying to perform Copy-Paste operation in a loop.

尝试一下

sht.Range("A2:AM" & LastRowWb).Copy
DoEvents
ws.Range("A" & LastRow).PasteSpecial Paste:=xlPasteFormats

另一方面，您可能还想阅读如何避免在Excel VBA中使用选择

On a side note, you may also want to read up on How to avoid using Select in Excel VBA

这篇关于为什么有时PasteSpecial方法会引发错误1004?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！