Solution
Dynamic transposition should be used, you can transpose manually or with a function Transpose
. In this example will be done one by one.
Code
Create an array with the data column and then insert one by one into another sheet.
Option Explicit
Sub test()
'Declarações
Dim Arr() As Variant
Dim LastRow As Long, j As Long, linha As Long, coluna As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
'Declara a planilha com os dados
Set ws1 = ThisWorkbook.Sheets("Planilha1")
Set ws2 = ThisWorkbook.Sheets("Planilha2")
'Em ws1:
With ws1
'ÚltimaLinha
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'Array
Arr() = .Range("A1:A" & LastRow).Value2
linha = 1
coluna = 1
'Loop em cada elemento da Array
For j = LBound(Arr) To UBound(Arr)
ws2.Cells(linha, coluna) = Arr(j, 1)
coluna = coluna + 1
'Quando preencher 5 células, passa para próxima linha e zera contador de coluna
If coluna = 6 Then
linha = linha + 1
coluna = 1
End If
Next j
End With
Application.ScreenUpdating = True
End Sub
Upshot
With the data in the spreadsheet Worksheet1:
+----+
| A |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
The result is as follows:
+----+----+----+----+----+
| A | B | C | D | E |
+----+----+----+----+----+
| 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 |
| 11 | 12 | 13 | 14 | 15 |
| 16 | 17 | 18 | 19 | 20 |
+----+----+----+----+----+
Explanation
ws1 and ws2
Declares the name of the two worksheets to be used.
Set ws1 = ThisWorkbook.Sheets("Planilha1")
Set ws2 = ThisWorkbook.Sheets("Planilha2")
Lastrow
Get the last row of the desired column, for example column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Arr
Create an Array() with the data of the desired column.
Arr() = .Range("A1:A" & LastRow).Value2
Loop
Loop from first to last array element
For j = LBound(Arr) To UBound(Arr)
Next j
Write to Worksheet
Write each element of the array to Worksheet 2
ws2.Cells(linha, coluna) = Arr(j, 1)
Condition
When the fifth element is filled in the row, Zera the column counter and moves to the next row.
If coluna = 6 Then
linha = linha + 1
coluna = 1
End If