Excel vba paste special method fails whenever i try to paste

Question

Hi everyone I'm fairly new at vba I'm trying to capture all ctrl+v events on my sheet and paste whatever is on the clipboard to the currently selected cell. Most of the time what i want to copy is text from firefox or from notepad E.I client's name or phonenumber that is n our website however the code only works when i try to paste inside de cell itself (in cell edit mode)

    Private Sub CopyPaste()

'PasteSpecial Values Only
  ActiveCell.PasteSpecial Paste:=xlPasteValues, skipblanks:=True

this returns a runtime error 1004 pastespecial method of range class failed i have also tried this but it returns a error too

activecell.PasteSpecial Format:="Text", skipblanks:=True, link:=False, DisplayAsIcon:=False

As a note the main reason for pasting text only and values only is because my excel has a very specific cell layout with colors and other stuff and a normal paste messes everything up.

I would love it if anyone could help me out

I should have clarified i am capturing all ctrl+v on a workbook_open sub Application.OnKey "^{v}", "CopyPaste" — Aeon
urdearboy oh so if i do not retrieve the data from the clipboard first i can't paster it as text ? so i need to create a msform obj and paste it there ? — Aeon

Daniel Dušek Daniel Dušek · Accepted Answer · 2018-11-09T22:57:00

Range.PasteSpecial method pastes a Range that has been copied into the specified Range. So this will work for Ranges only:

ActiveCell.PasteSpecial Paste:=xlPasteValues, skipblanks:=True

Clipboard needs to be accessed directly. How to use clipboard e.g. here.

Option Explicit

Sub Init()
    Application.OnKey "^{v}", "CopyPaste"
End Sub

Public Sub CopyPaste()       
    ' MSForms.DataObject can be used when MSForms lib. is referenced
    Dim clipboard As Variant 

    Dim strContents As String

    ' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
    ' When no MSForms is referenced yet.
    Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 
    clipboard.GetFromClipboard
    strContents = clipboard.GetText
    ' Parse or format strContent if needed
    ActiveCell.Value = strContents
End Sub

Excel vba paste special method fails whenever i try to paste

1 Answers