Sub Test()
Dim myArr() As Variant, myRng As Range
Dim iCol As Range, iRow As Range
Dim x As Long, y As Long, z As Long, i As Long
' сколько столбцов
 z =  15 
 
 myArr() = Selection.Value ' тут ваше заполнение массива
 
 x = Fix(UBound(myArr) / z)
 y = UBound(myArr) Mod z
 If y >  0  Then x = x +  1  Else: y = z
    
 With Range("A1")
  Set myRng = .Resize(x, y)
  If x >  1  Then Set myRng = Union(myRng, .Offset(, y).Resize(x -  1 , z - y))
  End With


 For Each iCol In myRng.Columns
  For Each iRow In iCol.Rows
   i = i +  1 
   iRow.Value = myArr(i,  1 ) ' возможно обращение к массиву будет джругим
   Next
 Next
 
End Sub