=ИНДЕКС($A:$A;10*(СТРОКА()-СТРОКА($B$1))+СТОЛБЕЦ()-СТОЛБЕЦ($B$1)+1)

'формирование по 10 столбцов
Sub transposition_2()
       Dim arrSrc() As Variant: Dim arrTrg() As Variant
       Dim rowMax As Long: Dim rowsTrg As Long: Dim colsTrg As Long
       Dim i As Long: Dim r As Long: Dim c As Long
          
       colsTrg = 10
       rowMax = Cells(Rows.Count, 1).End(xlUp).Row
       rowsTrg = WorksheetFunction.RoundUp(rowMax / colsTrg, 0)
          
       ReDim arrSrc(1 To rowMax, 1 To 1)
       ReDim arrTrg(1 To rowsTrg, 1 To colsTrg)
          
       arrSrc = Range(Cells(1, 1), Cells(rowMax, 1))
       For i = 1 To rowMax
           r = Int((i - 1) / colsTrg) + 1
           c = i - (r - 1) * colsTrg
           arrTrg(r, c) = arrSrc(i, 1)
       Next i
       With Range("B1").Resize(rowsTrg, colsTrg)
           .NumberFormat = "@"
           .Value = arrTrg
       End With
End Sub

Sub transposition_3()
     Dim rowMax As Long: Dim rowsTrg As Long: Dim colsTrg As Long
      
     colsTrg = 10
     rowMax = Cells(Rows.Count, 1).End(xlUp).Row
     rowsTrg = WorksheetFunction.RoundUp(rowMax / colsTrg, 0)
      
     With Range("B1").Resize(rowsTrg, colsTrg)
         .Formula = "=INDEX($A:$A,10*(ROW()-ROW($B$1))+COLUMN()-COLUMN($B$1)+1)"
         .Copy
         .PasteSpecial xlPasteValues
         Application.CutCopyMode = False
     End With
End Sub