Trying to find a code that changes the control V function to paste as values-CodePudding

I need to change control V to always paste as values, when I use the below code;

Public Sub PasteValue()
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

This works except when I try and use it from a web page, it either returns a 400 error or 1004 error. I have been able to figure out how to paste as values from a web page or from within the workbook but not both.

Any help would be appreciated.

Thanks' Shaune

CodePudding user response：

This is actually not an option.

Paste Special Values is reserved for copying and pasting from Excel to Excel. The whole point of it is to copy a result from a formula in a cell to it's textual/numerical representation to another or the same cell so as to remove the reference on the formula.

If you try and do this in Excel directly, you'll get this result ...

Copy -> Paste Special -> Values from a Web Page

This is the option you will see, there is no ability to paste special values because everything is a value. It's just a matter of what formatting you want to apply from the source data.

Copy -> Paste Special -> Values from a Range

This is where you get the option because the source being copied from is a range of cells.

This will need to be enhanced so you get exactly what you want but you should be able to cater for both scenarios with something like this ...

Public Sub PasteValue()
    On Error Resume Next
    
    Selection.PasteSpecial Paste:=xlPasteValues
    
    If Err.Description = "" Then Exit Sub
    
    Selection.PasteSpecial Paste:=xlPasteAll
End Sub