Copy row to column recursively using macro?

[raghavme]

Hi guys, I wish to copy data from row to columns recursively. All I know is how to record macros. Kindly, help me edit the code.

Eg.

The data is in the following format, it is in one single column.

BRANCH1
ROW11
ROW12
ROW13
ROW14
ROW15
ROW16
;
BRANCH2
ROW21
ROW22
ROW23
ROW24
ROW25
ROW26
;
BRANCH3
ROW31
ROW32
ROW33
ROW34
ROW35
ROW36
;

I want to copy the data in a new sheet as

BRANCH1  ROW11  ROW12  ROW13  ROW14  ROW15  ROW16
BRANCH2  ROW21  ROW22  ROW23  ROW24  ROW25  ROW26
BRANCH3  ROW31  ROW32  ROW33  ROW34  ROW35  ROW36

The macro I recorded is like:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 8/14/2009 by
'
' Keyboard Shortcut: Ctrl+n
'
    Range("A1:A6").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
 
End Sub

Please help me make this auto increment, I tried using offset, but, I could figure out proper way to use that.

 

[Peter_SSs]

A few questions:

1. What is the name of the sheet this data is in?

2. Can we assume the new sheet where you want the rearranged data to end up has been created already? What is its name?

3. Are the row numbers shown in your sample correct? And does that therefore mean there are blank rows between each 'section'?

4. Do the cells containing this data have formulas in them or is this data just straight text?

5. What column is the data in?

6. Should the resultant data in the new sheet start in cell A1?

 

[raghavme]

1. What is the name of the sheet this data is in?
The main sheet is "old" where this row data is stored
The fresh sheet is "new" where the new data has to be stored

2. Can we assume the new sheet where you want the rearranged data to end up has been created already? What is its name?
its called "new" created already

3. Are the row numbers shown in your sample correct? And does that therefore mean there are blank rows between each 'section'?
I wrote just for reference, please see the screen shot


4. Do the cells containing this data have formulas in them or is this data just straight text?
Its just data, no forumlas

5. What column is the data in?
col A

6. Should the resultant data in the new sheet start in cell A1?
Yea no issues, the fresh sheet is blank

 

[Peter_SSs]

Ok, so it looks like there are exactly 7 rows per 'section' and I'm assuming you don't want the ";" row in the 'New' sheet.

If all that is true, then try this as one way:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyToRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Sheets("Old").Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("New")<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A1").Resize(lr / 7, 6)<br>            .Formula = "=INDEX(Old!C1,(ROWS(R1C:RC)-1)*7+COLUMNS(RC1:RC))"<br>            .Value = .Value<br>            .EntireColumn.AutoFit<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[raghavme]

Thanks for the help!
It works great, however, a small glitch exists.

its coming like this


However, if I delete the lines having the semicolonit works great!

Can you please suggest a tweak? thanks a ton again!

 

[Peter_SSs]

Thanks for the help!
It works great, however, a small glitch exists.

its coming like this


However, if I delete the lines having the semicolonit works great!

Can you please suggest a tweak? thanks a ton again!

The sample data you posted in your first post had 8 rows per section (semicolons on rows 8, 16 and 24) . The sample data you posted in the image linked to in post #3 had 7 rows per section (semicolons on rows 7, 14 and 21).

It looks to me like you have run the code from my last post (which I pointed out was for 7 rows per section) on data that has 8 rows per sections.

Is that the case? Does you data have a set number of rows per section? If so, how many?

 

[raghavme]

My apologies! I created an quick example to test the code that you had posted. Therefore, got it wrong!

It works just great! Thanks for your help!