CODEWITHSUNDEEP
excelvbapaste
Afshin Davoudy
Afshin Davoudy

Reputation: 161

VBA Paste clipboard content (only value)

I'm trying to paste clipboard content to cell "A1"

Range("A1").PasteSpecial xlPasteValues

But I get "PasteSpecial method of Range class failed" error!

Appreciate any help!

Upvotes: 0

Views: 13276

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Paste only Values from Selection

Error

Run-time error '1004':

PasteSpecial method of Range class failed

Fact

xlPasteValues is exclusively used after (contents of) cells have been copied (into the clipboard).

Problem

The error always occurs after no cells have been copied or cells have been cut.

Solution

Therefore you should check (test) CutCopyMode appropriately and your formula will be fine: 

Sub PSpec()
     If Application.CutCopyMode = xlCopy Then
         Range("A1").PasteSpecial xlPasteValues
     End If
End Sub

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

Test this code, please (it works only for text clipboard type):

Sub testClipB()
 Dim CB As Object
 Set CB = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 CB.GetFromClipboard
 Range("A1").value = CB.GetText
End Sub

Or this one:

Sub testPasteClipB_Bis()
    Dim DataObj As MSForms.DataObject
    Set DataObj = New MSForms.DataObject
    DataObj.GetFromClipboard

    Range("A2").value = DataObj.GetText(1)
End Sub

Upvotes: 3

Related Questions