Transferring data from a column into several rows Closed

Question

I have data in a column on one sheet that needs to go into several rows in another sheet.

Example: Column A has the following data (A1:A200):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Need it to be place in rows in groups of 4:
ROW 1A: 1
ROW 1B: 2
ROW 1C: 3
ROW 1D: 4
ROW 2A: 5
ROW 2B: 6
ROW 2C: 7
ROW 2D: 8
ROW 3A: 9
ROW 3B: 10
ROW 3C: 11
ROW 3D: 12
ROW 4A: 13
ROW 4B: 14
ROW 4C: 15
ROW 4D: 16
ROW 5A: 17
ROW 5B: 18
ROW 5C: 19
ROW 5D: 20

Is there a way of doing this?

TrowaD · Answer

Hi LJ30,

The following code will copy range A1:A200 and paste it to B1:GS1.
The groups of 4 or cut/pasted to the range B1:E50.

So make sure those 2 ranges don't contain data, otherwise create a new sheet.

After the code has done it's work delete column A to get the desired result.

Here is the code:
Sub TransposeIn4()
Dim x, y As Integer
Range("A1:A200").Copy
Range("B1").PasteSpecial Transpose:=True
x = 2
y = 1
Do
    x = x + 4
    y = y + 1
    Range(Cells(1, x), Cells(1, x + 3)).Cut _
    Range("B" & y)
Loop Until y = 50
End Sub
To use the code:
Alt+F11 > top menu in new window > insert > module > paste code in big white field.
Back at excel at the correct sheet hit Alt+F8 and double-click TransposeIn4.

Best regards,
Trowa

Transferring data from a column into several rows

1 response

Similar discussions

Subscribe To Our Newsletter!