Intermittent "Run-time error '1004' PasteSpecial method of Range class failed

Question

I have a macro that intermittently throws a run-time error when doing a PasteSpecial. Most times it runs through to completion without failing, but sometimes it doesn't. When it throws the error, if I click on "Debug" and then just let it continue, it runs through without a problem. This sounds like a timing thing, but I don't understand what's causing it. Here is a snippet of the code:

Dim SourceDataWB    As Workbook
Dim RawDataWS       As Worksheet
Dim LastDataRow     As Long
Dim SrcRange        As Range

<Lots of other code in here...>

SourceDataWB.Activate
Set SrcRange = Range("A1:A" & LastDataRow)
SrcRange.Copy
RawDataWS.Range("A:A").PasteSpecial xlPasteValues

The RawDataWS worksheet is in a different workbook than the SourceDataWB. The error occurs on the PasteSpecial line. And if I just press "Play" at that point, it continues without error. Any ideas?

Answer 1

For anyone else experiencing a random/intermittent runtime error in VBA, one key may be to take control of the error handling. Instead of having your code go to this onscreen error, have VBA do something else.

One idea is -- when the error occurs -- have VB sleep for a moment and then retry the offending action. In many cases, that may be all you need to resolve the issue.

Using the above example, you might code:

' Tell VB not to handle errors with onscreen/debug behavior (just for now)
On Error Resume Next

' This is the example line that intermittently throws the runtime error
RawDataWS.Range("A:A").PasteSpecial xlPasteValues

' Check if there was an error and -- if so -- do something about it
If Err Then

   ' Pause
   Sleep (1000) ' Pause in milliseconds
                                    
   ' retry offending line...
   RawDataWS.Range("A:A").PasteSpecial xlPasteValues
                                    
                                    
   Err.Clear ' Clear Err object fields
End If
                                
On Error GoTo 0 ' set VB error handling back to normal

... of course this code assumes that it runs OK the second time. If failure that time is unacceptable, you may need to add a condition for that...