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?
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?
Code:
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