Why does "Paste Method of Worksheet class failed" occasionally occur?

Question

Some context: the following macro opens the most recent file in the specified directory. I am trying to copy all the data in the newly opened sheet into another worksheet. Sometimes, and only sometimes, I receive a 1004 error.

"Paste method of Worksheet class failed".

Sometimes the macro works. I cannot pinpoint why this happens.

Can anyone identify issues with the code? Clearing the clipboard works sometimes, but not always. Also, I have been using several macros just like this one (linked to different folders) in a larger macro. I run into the same problem occasionally.

Sub ImportOldRates()

    'Declare the variables
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date

    'Specify the path to the folder
    MyPath = "C:\Folder1\Folder2\"

    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    'Get the first Excel file from the folder
    MyFile = Dir(MyPath & "*.xls", vbNormal)

    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If

    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0

        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath & MyFile)

        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If

        'Get the next Excel file from the folder
        MyFile = Dir

    Loop

    'Open the latest file
    Workbooks.Open MyPath & LatestFile
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Run "ConnectChartEvents"
    Cells.Select
    Range("E2").Activate
    Selection.copy
    ActiveWindow.Close
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
    DisplayAsIcon:=False, NoHTMLFormatting:=True
    Application.CutCopyMode = False
    Selection.Columns.AutoFit
    Range("A1").Select
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

Answer 1

This would be better fitting as a comment, but I wanted to add my two cents and don't yet have the ability to comment here.

I encounter a similar error from time to time in macros I use to copy and paste charts from Excel into PowerPoint Presentations; for me at least, the error occassionally occurs when the macro attempts to paste into PPT, which lead me to believe that the issue lies with flipping between the Excel program and the PPT program.

Since I was too lazy to figure out a more effective way to bring images over to PPT, I added in the below line to avoid the error on most occasions.

Application.Wait (Now + TimeValue("0:00:01"))

I added this in immediately before the call is made to flip to PPT, and it greatly reduced the occurrence of the error (I very rarely get it now, and when I do running the macro again works fine). It basically just forces the macro to stop and wait 1 second before continuing, allowing the programs to catch up before it attempts to paste the data. There are more direct ways to accomplish this in theory, such as DoEvents which should make the macro wait until current processes are finished, but that never resolved this issue for me.

Answer 2

Thanks for everyone's contribution.

It turns out that the issue was caused by

 Range("E2").Activate

Copying the entire sheet would sometimes deplete my systems memory and cause the copy function to fail. For those interested, I simply selected a smaller range.

 Range("A1,Z400").Activate

This fixed the problem