Looking for better way to paste

[hitbid]

I am copying data from an outside excel source and then pasting that to excel.

I am fine clearing out the contents and the macro works as is, but the Paste function seems inadequate. I am trying to not use .select, and tried to incorporate CELLS vs Range, but nothing worked. The only thing I could use was the default from the macro recorder. Any idea how to enhance the Range("A2").select paste lines?

Dim lastrow As IntegerDim lastcol As Integer


Sub clear()


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column


Range(Cells(2, 1), Cells(lastrow, lastcol)).ClearContents
End Sub


Sub Paste()


'insert your paste statement here
'Does not seem like the ideal paste statement.
'sub is separated from previous sub because the contents that were copied were erased from memory after the 'above ran. 
Range("A2").Select
ActiveSheet.Paste


Dim i As Integer


For i = 2 To lastrow
    Cells(i, lastcol).Formula = "=B" & i & "&"" - ""&A" & i
Next i


End Sub

 

[decadence]

Perhaps this...

Option Explicit

Sub Paste()
'
    Dim lastrow As Integer, lastcol As Integer, i As Integer
    Dim strPaste  As Variant
    Dim DataObj As MSForms.DataObject
    Set DataObj = New MSForms.DataObject
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
    range(Cells(2, 1), Cells(lastrow, lastcol)).ClearContents
    
    DataObj.GetFromClipboard
    strPaste = DataObj.GetText(1)
        If strPaste = False Then Exit Sub
        If strPaste = "" Then Exit Sub
    [a2] = strPaste
    Set DataObj = Nothing
    
        For i = 2 To lastrow
            Cells(i, lastcol).Formula = "=B" & i & "&"" - ""&A" & i
        Next i
End Sub

 

[hitbid]

Thanks, but I don't get the option in the intellisense for MSForms. The user defined type is not defined.