How to paste directly from clipboard using VBA

[SDPanda]

Hi,

New to VBA. My goal is to automatically paste (values only) copied from an external table into cell A1 of Sheet 1 upon opening the Workbook.

I have figured out how to set the active sheet/cell and paste directly from clipboard using MS.Forms.DataObject. The problem I have is that multiple rows/columns of data are pasted into a single cell. If I try text to columns only the first row of data is shown, multiple times.

Public Sub Worksheet_ActivateAndPasteSpecial()

    Sheet1.Activate
    Range("A1:ZZ1000").Select
    Range("A1").Activate
    
End Sub

Sub PasteSpecial_from_Clipboard()

Dim CObj As MSForms.DataObject
Set CObj = New MSForms.DataObject
CObj.GetFromClipboard
XText = CObj.GetText(1)
ActiveSheet.Range("A1").Value = XText

End Sub

 

[rakesh seebaruth]

Sub PasteValues()
    Dim DataObj As MSForms.DataObject
    Set DataObj = New MSForms.DataObject

    'Get data from clipboard
    DataObj.GetFromClipboard

    'Split the data into rows and columns
    Dim PasteData As Variant
    PasteData = DataObj.GetText(1).Split(vbCrLf)

    'Paste data into cell A1 of the active sheet
    Dim r As Long
    For r = 0 To UBound(PasteData)
        If r > 0 Then
            'Insert a new row for each row of data
            ActiveSheet.Range("A1").EntireRow.Insert
        End If
        'Paste the values into column A of the new row
        ActiveSheet.Range("A" & r + 1).Value = PasteData(r)
    Next r
End Sub

 

[rakesh seebaruth]

You can add the following also

Private Sub Workbook_Open()    
Call PasteValues 
End Sub