Move data from first 7 rows from one column A into multiple Columnsthen the next 7 rows

[dids72]

Hello,

I am trying to figure out if it is possible to code a VBA macro (and if so how to do it) to take the first 7 rows of data from Column A and move it to the first row of Columns B,C,D,E,F,G,H and then proceed to the next 7 rows in Column A and move the data to the next available rows in B,C,D,E,F,G,H.

Example:

A1 move to B1
A2 move to C1
A3 move to D1
A4 move to E1
A5 move to F1
A6 move to G1
A7 move to H1

A8 move to B2
A9 move to C2
A10 move to D2
A11 move to E2
A12 move to F2
A13 move to G2
A14 move to H2

A15 move to B3
A16 move to C3
A17 move to D3
A18 move to E3
A19 move to F3
A20 move to G3
A21 move to H3

and so on until there is no more data in Column A.


Any help would be greatly appreciated.


Thanks,

DIDS

 

[Claus Busch]

Hi,

Am Mon, 7 Jan 2013 10:47:28 -0800 (PST) schrieb (e-mail address removed):

A1 move to B1
A2 move to C1
A3 move to D1
A4 move to E1
A5 move to F1
A6 move to G1
A7 move to H1

try:

Sub MoveIt()
Dim LRow As Long
Dim i As Long
Dim k As Long

k = 1
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow Step 7
Range("A" & i).Resize(7, 1).Copy
Cells(k, 2).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
k = k + 1
Next
End Sub


Regards
Claus Busch

 

[dids72]

Hi Claus,

That worked perfect. Thank you very much for your help.

Thanks,

DIDS