Single column of data (= 7700 rows) into 3 columns - lots of 66

[ad02916]

Hi All,

I’ve spent a lot of time searching the web looking for a Marco that will put an export of my music directory into 3 columns. Unlike other examples and posts I’ve read, I would like the Macro to copy and paste a set number of rows (66) into the next column only 3 columns are required. So after each copy & paste it looks for the next 66 rows heading down the column.

Eg (Rows 1 – 66 = C1), (Rows 67 - 132 = C2), (Rows 133 – 198 = C3) etc….

Example:
1
2
3
.
.
.
7700

into​

C1 C2 C3​

1 67 133
2 68 134
3 69 135
4 70 136
5 71 137
6 72 138
. . .
. . .
. . .
66 132 198
​

C1 C2 C3​

199 265 331
200 266 332
201 267 333
202 268 334
203 269 335
204 270 336
. . .
. . .
. . .
264 330 396

I thought this type of question would have been common, not the case.

Cheers – ad02916​

 

[Peter_SSs]

Hi All,

I’ve spent a lot of time searching the web looking for a Marco that will put an export of my music directory into 3 columns. Unlike other examples and posts I’ve read, I would like the Macro to copy and paste a set number of rows (66) into the next column only 3 columns are required. So after each copy & paste it looks for the next 66 rows heading down the column.

Eg (Rows 1 – 66 = C1), (Rows 67 - 132 = C2), (Rows 133 – 198 = C3) etc….

Example:
1
2
3
.
.
.
7700

into​

C1 C2 C3​

1 67 133
2 68 134
3 69 135
4 70 136
5 71 137
6 72 138
. . .
. . .
. . .
66 132 198
​

C1 C2 C3​

199 265 331
200 266 332
201 267 333
202 268 334
203 269 335
204 270 336
. . .
. . .
. . .
264 330 396

I thought this type of question would have been common, not the case.

Cheers – ad02916​

Welcome to the MrExcel board!

I'm a little confused by your example.

1. After the macro, does C1 hold 1 or 199? See red text above.

2. Also, by C2 and C3, do you really mean D1 and E1 instead?

 

[ad02916]

I apologise for my confusing example, only new to forums. Should have used the following to explain my issue. C1 = column A, C2 = column B, C3 = column C.

So the answer to your question does C1 hold 1 or 199 is ‘yes’ If you were to print 2 pages out of my example. '1' would be on the 1<SUP>st</SUP> page Column A row 1, '199' would be on page 2 Column A row 199.

I’m hoping this is making sense? Thanks for the reply​

 

[Marcelo Branco]

Hi,

Assuming data in Sheet1, beginning in row 1, and copy in Sheet2 maybe this

Sub copyCols()
    Dim i As Long, col As Long, lin As Long
 
    With Sheets("Sheet1")
        col = 1
        lin = 1
        For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row Step 66
            .Range("A" & i & ":A" & i + 65).Copy Sheets("Sheet2").Cells(lin, col)
            col = col + 1
            If col > 3 Then col = 1: lin = lin + 66
        Next i
    End With
 
End Sub

HTH

M.

 

[ad02916]

OMG! You've done it! So greatful for you time & effort, if I could buy you a beer I would. You have saved me so much time. I can now print off my entire music collection all formatted in the blink of an eye.

You L E G E N D!!!

Thanks - ad02916

 

[Marcelo Branco]

OMG! You've done it! So greatful for you time & effort, if I could buy you a beer I would. You have saved me so much time. I can now print off my entire music collection all formatted in the blink of an eye.

You L E G E N D!!!

Thanks - ad02916

You are welcome and tks for the feedback
(waiting for the beer )

M.