Macro That Puts Selected Cell Data Into Clipboard and Then Clears Cells

[miketurn]

On a different thread of mine, a very helpful member by the name of @Eric W helped me out with a particular macro, to solve a bug type issue I was having.
This macro got me thinking of another possible use for it.

1.) I am looking to be able to select a cell or multiple cells that contain text
2.) Run macro that copies this text into the clipboard
3.) Clears the texts from the selected cells
4.) Now with the text in the clipboard, allows you to paste it into another cell / cells if selection contained more than one cell.

Here is a piece of the code that copies the text data into the clipboard

Sub AddToClipboardThenClearCell()
    
   
    On Error Resume Next
    Set clipboard = New dataobject
    clipboard.getfromclipboard
    mydata = clipboard.gettext
    
    clipboard.settext ""
    clipboard.putinclipboard
    
    
    Set clipboard = New dataobject
    clipboard.settext mydata
    clipboard.putinclipboard
    
End Sub

I tried adding the following line of code in a couple of different areas but unfortunately did not work.

Selection.ClearContents

If anyone has an idea of how I can meld the two together, it would be really appreciated.

Thank you to anyone who gives it a shot.

 

[Marcelo Branco]

You can use the function RangeToClipboardString created by Chip Pearson
http://www.cpearson.com/excel/clipboard.aspx

Maybe something like this

Sub aTest()
    'Insert a reference to Microsoft Forms 2.0 library: FM20.dll
    Dim DataObj As New MSForms.DataObject
    Dim strRange As String
    
    strRange = RangeToClipboardString(Selection)
    DataObj.SetText strRange
    DataObj.PutInClipboard
    Selection.ClearContents
End Sub

Public Function RangeToClipboardString(RR As Range) As String
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' RangeToClipboardString
    ' This function changes the cells in RR to a String that can be put in the
    ' Clipboard. It delimits columns with a vbTab character so that values
    ' can be pasted in a row of cells. Each row of vbTab delimited strings are
    ' delimited by vbNewLine characters to allow pasting accross multiple rows.
    ' The values within a row are delimited by vbTab characters and each row
    ' is separated by a vbNewLine character. For example,
    '   T1 vbTab T2 vbTab T3 vbNewLine
    '   U1 vbTab U2 vbTab U3 vbNewLine
    '   V1 vtTab V2 vbTab V3
    ' There is no vbTab after the last item in a row and there
    ' is no vbNewLine after the last row.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim R As Long
    Dim C As Long
    Dim s As String
    For R = 1 To RR.Rows.Count
        For C = 1 To RR.Columns.Count
            s = s & RR(R, C).Value & _
                IIf(C < RR.Columns.Count, vbTab, vbNullString)
        Next C
        s = s & IIf(R < RR.Rows.Count, vbNewLine, vbNullString)
    Next R
    RangeToClipboardString = s
End Function

1. Select the origin range and run the macro aTest.
2. Select the destination cell and paste (Ctrl+V)

Hope this helps

M.

 

[miketurn]

@Marcelo Branco
Thank You for your response, I tested it out and at first I was like, what did this do? and then I realized I was asking this question because it worked so smoothly and simply at accomplishing what I was looking for it seemed like it was not doing anything at all
Thank you again for this it works great

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.