The code below is trying to paste the selected range (passed as rng
) to the end of the worksheet.
It works if there are two rows already present (A1, A2).
Sub copyRow(rng As Range, ws As Worksheet)
Dim newRange As Range
Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0)
rng.Copy
newRange.PasteSpecial (xlPasteAll)
End Sub
If A1 and A2 are present and you call this method 100 times, it inserts 100 rows after them.
If no rows are present or only A1, it just overwrites A2. I could see Excel write on the same row (overwrite).
Appears something to do with how xlDown calculates if there are less than 2 rows.
Source: Stackoverflow.com