VBA: Paste in a single cell NOT multiple rows

[pivotpp]

Hi all,
This is a seemingly simple operations but can't figure it out.
Goal: I have some content copied, I want to paste it into a single cell.
The default when I control+paste into excel is that is paste it as multiple rows/lines.
Even when I right click for more paste options, there isn't any, I only see the paste as text which is the above, paste into several rows.
THE ONLY WAY, I can do is is by double-clicking into the cell and then control-paste OR press F2 (which is the same as double-clicking into a cell) and pasting.
Any others ways?
Since this double-clicking/F2 method is the only one I know...I'm having trouble replicating this as VBA code.
Again, ultimately I just want a VBA script to paste my content into a single cell.
Any help/input much appreciated. Thanks!

 

[JustynaMK]

Hi pivotpp,

You can use the following code to replicate F2 & Ctrl+V functionality (so that it pastes content into a single cell):

Public Sub PasteContent()
    Dim dob         As MSForms.DataObject
    
    Set dob = New MSForms.DataObject
    
    With dob
        Call .GetFromClipboard
        ActiveCell.Value = Replace$(.GetText, vbTab, " ")
    End With
    
    Set dob = Nothing
End Sub

You need to make sure that you have Object Library checked in References window:
VBA window > Tools > References > "Microsoft Forms 2.0 Object Library"

Let me know if it works for you.