Paste Values Macro Doesn't Work for Some Content on Clipboard

[agrarianaardvark8596]

Hello,

I'm trying to create a macro that pastes content from the clipboard into Excel using paste values, in a single key combination. I'm working on a project that involves thousands of individual acts of copying content from various sources and pasting values (no formatting). Using the CTRL+V, then CTRL, then V method is too slow, so I'd like to use a single combination, e.g. CTRL+SHIFT+V, to paste values instead. I'm using the VBA code below in my personal macro workbook:

Sub paste_val_exper_one()
	Range("b2:b5001").Select
	ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        	DisplayAsIcon:=False, NoHTMLFormatting:=True
End Sub

This works for pasting content from other programs, like save paths copied from Windows Explorer or lines from a webpage, but when I paste content from multiple cells in the same worksheet, I get the following error:
"Run-time error '1004': PasteSpecial method of Worksheet class failed"

I tried the below code as well. It's kind of like the inverse - copying from worksheet cells works, but copying from other programs does not:

Sub paste_val_exper_two()
    ActiveSheet.Cells(2, 2).PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

When pasting from Windows explorer, I get the following error:
"Run-time error '1004': PasteSpecial method of Range class failed"

Restarting my PC didn't fix this. When I press F8 to debug, it highlights the code, and when I press F8 again, it gives me the respective error. I don't know what issue is being identified, though. I've tried code from a few other threads/forums online, but none get me past this issue.

I'd like to be able to use the macro with content from any source. I'd also like to be able to use this macro in any workbook I start, using the code I entered in my personal macro workbook. I'm very new to VBA, so please let me know if you have any ideas!

Thanks.

[Artik]

In general, such a procedure is enough for me:

Sub ShiftCtrlV()
    On Error Resume Next
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
                             DisplayAsIcon:=False, NoHTMLFormatting:=True
    If Err.Number = 0 Then GoTo CtrlV_Exit
    Err.Clear
    Selection.PasteSpecial Paste:=xlPasteValues, _
                           Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    If Err.Number = 0 Then GoTo CtrlV_Exit
    Err.Clear
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
                             False, NoHTMLFormatting:=True
CtrlV_Exit:
    On Error GoTo 0
End Sub

In addition, it would be good to define the procedure assignment to the keyboard shortcut. You should think about where you will enable this shortcut

Application.OnKey "+^v", "ShiftCtrlV"

and where you will disable it

Application.OnKey "+^v"

Artik

[agrarianaardvark8596]

Sorry for the delay - thank you so much, Artik! This is great.

I'm not sure what you mean about where I should enable and disable the shortcut. Are you referring to which workbooks I should have this or not?

Again, thanks a million!