Excel VBA - paste text from clipboard (error 1004)

Question

can someone help me with this code?

Sub TEST()
Dim Val As Variant
Sheets("Sheet 3").Select
Val = Range("A2").Value
Sheets("Sheet 1").Select
Range("AY" & Val).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Sheet 3").Select
Application.CutCopyMode = False
End Sub

I have to transfer simple number from web to excel. I need to copy number from web into a clipboard, then go to excel and run Macro. This macro should go to "sheet 3" set "Val" based on A2 value, go to sheet 1, select range in AY & "Val" and paste to this cell data (the number) from clipboard.

But when macro reach line 7 (Selection.PasteSpecial) Im getting Error:

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

Where I have the bug, please :)

First of all, if you want to fix your question, don't do it in comments - edit it. Second of all, share the exact error, with the traceback; it will show where exactly the error happens. — Itamar Mushkin
Its my observation.. When you run a macro, clipboard is emptied. Hence, there will be nothing in the clipboard to paste. You better include code to get the values from the web site in your macro like web scraping in vba — Naresh
@Naresh Im running on Windows 10 and it looks like Excel didn't see clipboard information copied somewhere else (out of excel). When I copy something from notepad to clipboard, then go to excel, run macro (get the error), I can go back to notepad and paste clipboard content (clipboard is not empty). — Andrew

Zwenn Zwenn · Accepted Answer · 2020-03-04T09:39:58

You must use a MSForms.DataObject to interact with the clipboard:

Sub TextFromClipboard()
'This works only with text!
  Dim oData As Object

  'New MSForms.DataObject with guid and late binding
  Set oData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  'Get text from clipboard
  'to the DataObject
  oData.GetFromClipboard

  'Show text
  MsgBox oData.GetText
End Sub

To put text to clipboard you can use the following 2 methods:

oData.SetText sText
oData.PutInClipboard

Excel VBA - paste text from clipboard (error 1004)

1 Answers