Hi,

To begin with, your code could be significantly shorter & lots faster

Dim x As Long
'lets start at row 2
x = 2

'pasting Sheet1!AT1 to Sheet2!A2
Worksheets("sheet2").Cells(x,1).Value =
Worksheets("sheet1").Cells(1,46).Value

'pasting Sheet1!AT2 to Sheet2!D2
Worksheets("sheet2").Cells(x,4).Value =
Worksheets("sheet1").Cells(2,46).Value

By incrementing x by 1 you put the next one in the row below the
current one

ManualMan