Clipboard clears out so must create a separate routine

[hitbid]

I copy data from an outside source and paste into excel.

To make things easier, I created a macro that starts by clearing out the current contents of the sheet.

Then after that is done, I can copy the outside data, and run the FormInsert routine.

I had to create the separate FormInsert routine, because if I put them all into one, nothing gets pasted, which I suppose is because that is how excel and clipboard work.

Do you see any way to combine the two into one?

Dim lastrow As Integer
Dim lastcol As Integer


Sub ClearSheet()


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


'This function clears out the whole sheet.
'Add a -1 to the last col to keep the last column.
'may not need that though, if you use the below FormInsert function


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


Sub FormInsert()


'insert your paste statement here
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

 

[Fluff]

How about

Sub ClearSheet()
Dim i As Integer

lastrow = Cells(Rows.Count, "A").End(xlUp).row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
'This function clears out the whole sheet.
'Add a -1 to the last col to keep the last column.
'may not need that though, if you use the below FormInsert function
Range(Cells(2, 1), Cells(lastrow, lastcol)).ClearContents
Range("A2").PasteSpecial
For i = 2 To lastrow
    Cells(i, lastcol).Formula = "=B" & i & "&"" - ""&A" & i
Next i

End Sub

 

[hitbid]

Interesting, that worked.

So then, if you copy and paste from an outside source, it is ok to combine the two routines into one.

If you copy and paste inside excel though, no dice...?