6

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
TylerH
  • 20,799
  • 66
  • 75
  • 101
bpconnallon
  • 81
  • 1
  • 1
  • 4
  • 2
    `1` Maybe because the format of the data in clipboard is not "unicode" `2` Maybe because `ActiveWindow.Close` is clearing the clipboard and hence there is nothing to copy. I would recommend working with objects. You may want to see [This](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) link. That way you can immediately paste after you copy. – Siddharth Rout Jul 02 '15 at 20:57
  • 2
    Other things to try: besides turning off Alerts and Events, try turning off the following: Application.ScreenUpdating, Application.EnableAnimations, Application.Calculation = xlCalculationManual, then at Worksheet level: .DisplayPageBreaks = False, .EnableCalculation = False, .EnableFormatConditionsCalculation = False, .EnablePivotTable = False; and like @SiddharthRout suggested: don't rely or use the Selection object – paul bica Jul 02 '15 at 21:27
  • When it fails: are the source cells connected perchance? – LocEngineer Jul 06 '15 at 21:14
  • This sort of thing usually happens when, in your case, `ActiveSheet` is not "ready" to do the pasting, either because there's a dialog box being displayed (unlikely in your case) or because Excel is trying to do something else (like save a document.) I suspect `ActiveWindow.Close` followed immediately by `ActiveSheet.PasteSpecial` is the source of the problem. – LondonRob Jul 07 '15 at 09:34

4 Answers4

9

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.

Etheur
  • 347
  • 4
  • 10
  • Thanks! We were using a macro for over three years without issue and suddenly paste method failed for simple shapes. I added "DoEvents" after reading this and solved the issue! (Also explains how stepping through the code was working fine.) – Qudsia May 24 '23 at 20:29
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

bpconnallon
  • 81
  • 1
  • 1
  • 4
1

I've had this problem recently and none of the aforementioned fixes work completely consistently for me. I found a solution that I wanted to drop in this thread.

Implementing error handling to retry both the copy and the paste seems to be the most consistent solution. The more conservative among us might want to also implement a counter that limits the number of retries to maybe 100 or so.

This fixed my failures and as the garbage collection in Excel is dubious at best this may be the best solution available.

Jaywalk
  • 145
  • 6
0

I've had the same issue and wrapping the Copy & Paste in "DoEvents" did it for me. Error occured while I had a Progressbar in a Userform updating itself while running a for-loop.

...
DoEvents
Worksheets(1).Rows(i).Copy
Worksheets(2).Range("A" & i).PasteSpecial xlPasteAll
DoEvents
...
Tilly
  • 1