Sub test123()
For i = 3 To ActiveWorkbook.Sheets("Лист1").Cells(Rows.Count, 5).End(xlUp).Row
ActiveWorkbook.Sheets("Лист2").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = _
ActiveWorkbook.Sheets("Лист1").Cells(i, 5).Value
Next
For i = 3 To ActiveWorkbook.Sheets("Лист1").Cells(Rows.Count, 6).End(xlUp).Row
ActiveWorkbook.Sheets("Лист2").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = _
ActiveWorkbook.Sheets("Лист1").Cells(i, 6).Value
Next
MsgBox ("Ok!")
End Sub