Pasting As Values Only

MatthewNYC

New Member
Joined
Feb 19, 2017
Messages
18
Hi,

I am hooking into the CTRL + V paste shortcut with the following code and redirecting it to my custom "PasteAsValues" procedure:

Application.OnKey "^v", "PasteAsValues"

In Sub PasteAsValues I am using the following code to paste as values:

Selection.PasteSpecial Paste:=xlPasteValues

This works perfectly if the user copies cells within Excel and pastes them.

However, if I copy a value from NOTEPAD, and paste it into Excel, I get the following runtime error:

Run-time error '1004':


PasteSpecial method of Range class failed

How can I adjust my code so that it pastes as values no matter where the source is I'm copying from? (e.g. Excel, Notepad, a web browser, etc.).

Matthew
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I am not a real techie, but I believe any data copied from a source external to Excel will be pasted as value only anyhow, so maybe a syntax like
Code:
Sheets("Sheet1").Range("A1").Select
ActiveSheet.Paste
 
Last edited:
Upvote 0
See if this works for you :

Code:
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
    Private Declare PtrSafe Function GetClipboardOwner Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, lpdwProcessId As Long) As Long
    Private Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
#Else
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long
    Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare Function GetClipboardOwner Lib "user32" () As Long
    Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
    Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
#End If

Private Const CF_TEXT = &H1

Sub PasteAsValues()
    Dim lPid As Long
    Dim oDataObj As Object
    
    OpenClipboard 0
    If GetClipboardData(CF_TEXT) = 0 Then MsgBox "The clipboard is empty.": CloseClipboard: Exit Sub
    CloseClipboard
    GetWindowThreadProcessId GetClipboardOwner, lPid
    If GetCurrentProcessId = lPid Then
        If Application.CutCopyMode <> False Then
            ActiveWindow.RangeSelection.PasteSpecial Paste:=xlPasteValues
        End If
    Else
        If GetClipboardOwner <> 0 Then
            Set oDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
            oDataObj.GetFromClipboard
            ActiveWindow.RangeSelection = oDataObj.GetText
            Set oDataObj = Nothing
        End If
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top