If you were using this in one session you could use a static variable for the
row number. You would need a method for setting it the first time  it was used
then increment it each time it was used after that.

However if you close Excel, I think, you would lose the static. Not so good.

If the row  you want to paste  to is always the next row after the last used row
you could use


Range("AJ51").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True