EDIT
The OP diagnosed the problem as being a concurrency issue where the clipboard was not copied to in time for the paste operation. The code below will solve this issue while maintaining a responsive UI and reasonable timeout and notification.
On Error Resume Next
Dim TimeoutLimit
TimeoutLimit = 300
Dim TimeoutCounter
TimeoutCounter = 0
Do
Err.Clear
DoEvents 'Yield thread execution
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
TimeoutCounter = TimeoutCounter + 1
Loop Until (Err.Number = 0 Or TimeoutCounter > TimeoutLimit )
On Error GoTo 0
If TimeoutCounter > TimeoutLimit Then
MsgBox "Paste failed because of operation timeout", vbCritical
'Is this fatal? Abort by proper exiting...
'Exit Sub
'Exit Function
End If
I think your use of ActiveSheet.PasteSpecial is probably fine, based on the testing you've done and this MSDN Documentation. However, your problem might be that you're getting data incompatable with a text format as discussed below.
Regarding the method, the Worksheet.PasteSpecial method is entirely different from the Range.PasteSpecial. I find it odd that the error you're getting says that a range method failed if you're calling the worksheet method. If that's accurate, I suspect that the Worksheet method calls the range method at some point.
I can reproduce the specific error
PasteSpecial method of Range class failed
for Range.PasteSpecial under the following conditions:
- the clipboard is empty
- the data was not copied from an Office
Application.
Turns out, there is an office clipboard and a Windows clipboard. For instance, Application.CutCopyMode only controls the office clipboard. Range.PasteSpecial works off of the Office clipboard, while Workbook.PasteSpecial works off of the system or Windows clipboard. So it doesn't throw error if the office clipboard is empty, in fact, it throws an error for the opposite condition where an excel range is copied if it's being forced to use Text as its format.
PasteSpecial method of Worksheet class failed
It also throws this error if the clipboard data cannot be converted to text (like a picture). You can handle this by not specifying a format and a default format will be used. It won't necessarily be text then, but this resolves the error for pasting an Excel Range.
To illistrate this and check to see how your clipboard content is stored, view it from Excel as shown below.
Since it is possible that one thing or the other might be giving you different problems under different conditions, you could try to progress through your options like this...
On Error Resume Next
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
If Err > 0 Then
Err.Clear
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
If Err > 0 Then
Err.Clear
'You could also try to manually retrieve data from clipboard at this point
ActiveSheet.PasteSpecial
End If
End If
On Error GoTo 0
Like I said in my comments, PasteSpecial can be finicky. Thus I reccomended removing that as a factor and testing if you can directly access clipboard content like the code below (copied from here)...
Sub GetClipBoardText()
Dim DataObj As MSForms.DataObject
Set DataObj = New MsForms.DataObject '<~~ Amended as per jp's suggestion
On Error GoTo Whoa
'~~> Get data from the clipboard.
DataObj.GetFromClipboard
'~~> Get clipboard contents
myString = DataObj.GetText(1)
MsgBox myString
Exit Sub
Whoa:
If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Sub
See this documented case of the same error that was being caused by the clipboard being empty and how easily this can happen with Office Macros. You are copying within your macro, so I wouldn't expect this to be your problem. Also, this code snippet will protect against a null for the Range method but not for the Worksheet method, since it only checks the application's clipboard and not the system's.
If Application.CutCopyMode = True Then
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Else
MsgBox("There is no data on the clipboard to be pasted.")
End If
