# Thread: Turn columns into rows from on sheet to another

1. ## Turn columns into rows from on sheet to another

On Sheet1 there are several hundred rows. Row 1 is a column header row.
In columns F, H, J, L, N, P are data I want to copy and turn into 6 rows for each row (starting with row 2) of Sheet1 and paste the new set of rows into Sheet2.

For example, if there are 101 rows in Sheet1 (counting the header row), Sheet2 will now have 600 rows (and 1 column).

Does that make sense? Macro, I suspect.

2. Could you attach an example, I do not completely understand your setup or what you want...

Steve

3. I resolved this with a formula using INDIRECT and (2+INT((ROW()-1)/6)) to pick the rows.

=INDIRECT("Sheet1!F"&(2+INT((ROW()-1)/6))) then, changed F to H, etc. and filled the block of 6 down.

Any better ideas would, of course, be appreciated.

4. KW,

Here is a VBA approach that will take the values from each column that you specified on each row and transpose them vertically onto sheet 2 in column A; each value having its own row. Hope this is what you are looking for.

Maud

transpose1.png Transpose2.png

Code:
```Public Sub TransposeRows()
With Worksheets("Sheet2")
LastRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row
Row = 2
For I = 2 To LastRow
For J = 6 To 16 Step 2
.Cells(Row, 1) = Cells(I, J)
Row = Row + 1
Next J
Next I
End With
End Sub```
Although the images do no show it, the code is adjusted to include values in column F

5. Thanks, Maud. What's your feeling about my code versus the VBA approach?

6. Kevin,

FWIW...My general opinion is if it works all's good! That said the VBA has the advantage (with some minor changes, adding arguments and/or prompts for source/dest addresses) of being stored in your Personal.xls file and then can be used on any worksheet with out having to recreate a complex formula in a new environment. HTH

7. I just prefer VBA because it is too easy to mistakenly overwrite a formula which can bring down the whole spread sheet so there is no need to lock down the worksheet. VBA is so much more versatile and easier to trouble shoot particularly when many formulas are embedded within formulas. You can make things happen when you want them to occur as well as not making them occur when you don't want them to.

In your instance, if that is all you will be using the cell for, you can guarantee that it will not be overwritten, and you want it to copy the values immediately then your code is as good as any VBA script. If you will be adding or deleting rows on the source sheet then just use caution in your referencing.

And ditto everything RG said.

Maud

8. Thanks both of you for the insight.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•