Copy value to next empty cell in same row and return average.

Hello,

I did try search and google, but the solutions I found are all for next rows. I want a macro that copies the value of Sheet1 A1 into the next empty cell in Sheet2 A1. When running the macro again, it pastes to B1,C1, etc..

Every time I searched for a solution, I get codes for the next row, but not the next column.

Code:

---

Range("A1").Copy
Sheets("Sheet2").Select
With Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteAll
    .PasteSpecial Paste:=xlPasteColumnWidths
End With
End Sub

---

I understand that the above code is set on A. So at this time I have to manually copy and transpose the data to a different sheet in order to get my data.

Also if data is entered in Sheet1 A2, then this would copied to Sheet2 A2,B2,... (this would be second macro). If possible I would like to limit every macro to their specific rows between the different sheets

Thnx in advance,

RR