Sub CopyDataToSheet2()
    Dim Thisrange As Range
    Dim cel As Range
    Dim vRow As Variant
    Dim j As Integer
    Dim iRow As Integer
    
    Set Thisrange = Selection
    
    If Thisrange.Columns.Count > 1 Then
        MsgBox "Only columns selection is permitted."
        Exit Sub
    End If
    
    With Sheet2
        .Select
        .Cells(65536, 1).Select
        Selection.End(xlUp).Select
        iRow = ActiveCell.Row + 1
        For Each cel In Thisrange
            j = j + 1
            .Cells(iRow, j) = cel
        Next cel
    End With
End Sub

Sub test()
    Sheet1.Range("A1:A4").Copy
    Sheet2.Range("A65536").End(xlUp).Offset(1).PasteSpecial Transpose:=True
End Sub