CODEWITHSUNDEEP
vbaexcelcopy-paste
cheezsteak
cheezsteak

Reputation: 2917

Improving PasteValue macro to work with Cut

I have the following simple VBA routine bound the CTRL+Shift+V for copy and pasting by value in Excel

Sub PasteValue()
    Selection.PasteSpecial _
         Paste:=xlPasteValues _
        , operation:=xlNone _
        , SkipBlanks:=False _
        , Transpose:=False
End Sub

I when attempting to paste a value from a cut instead of a copy I receive the following error,

Error 1004, PasteSpecial Method of Class Range failed"

How can I modify the above procedure to work when cutting as well as copying?

Upvotes: 2

Views: 96

Answers (2)

Mark Moore
Mark Moore

Reputation: 510

The below solution may be a bit overkill and also relies on you using the "CutSelection" macro in place of your Ctrl+X shortcut (or a different key binding if desired of course). As mentioned in the comments, it uses Chip Pearsons Clipboard module to copy your selected range to the clipboard, and additionally sets a variable to inform the paste routine that a "cut" is required. And then the paste routine simply does a paste, and queries the PasteMode, and if applicable will clear the original source contents (simulating a "cut"). Forgive me if teaching to suck eggs, but these need to be in the same module as the paste relies on the module level variables to clear the original data.

Dim PasteMode As String 'Paste mode used by paste value to decide if the source data should be cleared
Dim CopiedRange As String 'Store in case clearing source data
Dim CopiedFromWorkSheet As String 'Store in case clearing source data
Dim CopiedFromWorkbook As String 'Store in case clearing source data
'
Sub CutSelection()
'This macro should be used as an alternative to Ctrl+X when Cutting cells to be pasted elsewhere
'It relies on Chip Pearsons excellent Clipboard code avaiulable from
'http://www.cpearson.com/excel/Clipboard.aspx  and the MS forms library (see Chips site for details)
Dim MySelectedRange As Range
Dim MySelectedAsString As String
Dim DataObj As New MSForms.DataObject
    Set MySelectedRange = Selection
    CopiedFromWorkSheet = ActiveSheet.Name
    CopiedFromWorkbook = ActiveWorkbook.Name
    CopiedRange = Selection.Address
    MySelectedAsString = RangeToClipboardString(MySelectedRange)
    DataObj.SetText MySelectedAsString
    DataObj.PutInClipboard
    PasteMode = "Cut"
End Sub

Sub MyPastevalue()
    ActiveCell.PasteSpecial
    If PasteMode = "Cut" Then
        Workbooks(CopiedFromWorkbook).Sheets(CopiedFromWorkSheet).Range(CopiedRange).ClearContents
    End If
End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

This is probably not what you want to hear, but Paste Special doesn't work with Cut in Excel when done manually (the "Paste Special..." context menu is grayed out).

I don't think it's possible with VBA; I'd just wrap your code in an error-suppressing block:

Sub PasteValue()
    On Error Resume Next
        Selection.PasteSpecial _
             Paste:=xlPasteValues _
            , operation:=xlNone _
            , SkipBlanks:=False _
            , Transpose:=False
    Err.Clear
    On Error GoTo 0
End Sub

Upvotes: 1

Related Questions