Move next column to next row using macro?

[corescript]

Dear all
i've data like this:

A            B         C                D 
1 John       Willy      Diana            Chan Yu
2 male       male      Female            Male
3 married    single    Single            Single
4 Ohio       Texas     Las Vegas         New York
5 yes        NA        Yes               No

and i want to move next column to next row such as below

   A         B          C         D
1 John    
2 male    
3 married 
4 Ohio    
5 yes     
6 Willy 
7 male  
8 single
9 Texas 
10 NA
11 Diana        
12 Female   
13 Singe    
14 Las Vegas
15 Yes
16 Chan Yu      
17 Male
18 Single
19 New York
20 No

thank you

 

[corescript]

try this

Sub MM1()
Dim x As Long, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To lr Step 6
   Range("A" & x).Resize(6, 1).Copy
   Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
Next
Columns("A:A").EntireColumn.Delete
End Sub

helo MM what i mean is fill rows automatically with number

      A
1     1
2     1
3     1
4     1
5     1
6     1
7     2
8     2
9     2
10    2
11    2
12    2
13    3
14    3
15    3
16    3
17    3
18    3
19    4
20    4
21    4
22    4
23    4
24    4
25
26

and so on

 

[Michael M]

So that it looks like this ??
If not, you need to provide a before and after example

Excel 2007
	A	B	C	D	E	F
2	1	1	1	1	1	1
3	2	2	2	2	2	2
4	3	3	3	3	3	3
5	4	4	4	4	4	4
Sheet1

 

[corescript]

before

[TABLE="class: grid, width: 70"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

after

[TABLE="class: grid, width: 70"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

and so on

Thank you
Best Regards

 

[Michael M]

Sub MM1()
Dim x As Long, n As Integer
n = 1
For x = 1 To 90 Step 6 'change the 90 to the number of rows you need
   Range("A" & x).Resize(6, 1).Value = n
   n = n + 1
Next
End Sub

 

[corescript]

Sub MM1()
Dim x As Long, n As Integer
n = 1
For x = 1 To 90 Step 6 'change the 90 to the number of rows you need
   Range("A" & x).Resize(6, 1).Value = n
   n = n + 1
Next
End Sub

SMART that the word I want to say to you Michael M

thank you

Best Regards

 

[Michael M]

thank you...glad it worked..