splitting long columns
 

I have a spreadhseet that has about 800+ rows (2 columns), which takes
several pages. I am convering this sheet to PDF. Is there a way, within
Excel, that I can automaticaly split after a set number of rows, copy the
data and paste it to the top of the page in different columns? This way
..intead of 2 column x 800 rows (and 15 pages), I would have 8 columns x 200
rows (and 4 pages).

splitting long columns
 

Assuming your data is in cols A & B, try this small macro:

Sub Columnize()
n = Cells(Rows.Count, "A").End(xlUp).Row
k = 1
j = 1
For I = 1 To n
Set r = Range(Cells(I, 1), Cells(I, 2))
r.Copy Cells(k, j)
k = k + 1
If k = 201 Then
k = 1
j = j + 2
End If
Next
Range("A201:B" & n).Clear
End Sub

--
Gary''s Student - gsnu200851


"richzip" wrote:

I have a spreadhseet that has about 800+ rows (2 columns), which takes
several pages. I am convering this sheet to PDF. Is there a way, within
Excel, that I can automaticaly split after a set number of rows, copy the
data and paste it to the top of the page in different columns? This way
.intead of 2 column x 800 rows (and 15 pages), I would have 8 columns x 200
rows (and 4 pages).

splitting long columns
 

Thank you ..that works great!

Is there a way I can modify the macro so that it skips one column between
each pair that is copied?


"Gary''s Student" wrote:

Assuming your data is in cols A & B, try this small macro:

Sub Columnize()
n = Cells(Rows.Count, "A").End(xlUp).Row
k = 1
j = 1
For I = 1 To n
Set r = Range(Cells(I, 1), Cells(I, 2))
r.Copy Cells(k, j)
k = k + 1
If k = 201 Then
k = 1
j = j + 2
End If
Next
Range("A201:B" & n).Clear
End Sub

--
Gary''s Student - gsnu200851


"richzip" wrote:

I have a spreadhseet that has about 800+ rows (2 columns), which takes
several pages. I am convering this sheet to PDF. Is there a way, within
Excel, that I can automaticaly split after a set number of rows, copy the
data and paste it to the top of the page in different columns? This way
.intead of 2 column x 800 rows (and 15 pages), I would have 8 columns x 200
rows (and 4 pages).

splitting long columns
 

Just a one line change:

Sub Columnize()
n = Cells(Rows.Count, "A").End(xlUp).Row
k = 1
j = 1
For I = 1 To n
Set r = Range(Cells(I, 1), Cells(I, 2))
r.Copy Cells(k, j)
k = k + 1
If k = 201 Then
k = 1
j = j + 3
End If
Next
Range("A201:B" & n).Clear
End Sub

In this version we bump j by 3 rather than 2.
--
Gary''s Student - gsnu200851


"richzip" wrote:

Thank you ..that works great!

Is there a way I can modify the macro so that it skips one column between
each pair that is copied?


"Gary''s Student" wrote:

Assuming your data is in cols A & B, try this small macro:

Sub Columnize()
n = Cells(Rows.Count, "A").End(xlUp).Row
k = 1
j = 1
For I = 1 To n
Set r = Range(Cells(I, 1), Cells(I, 2))
r.Copy Cells(k, j)
k = k + 1
If k = 201 Then
k = 1
j = j + 2
End If
Next
Range("A201:B" & n).Clear
End Sub

--
Gary''s Student - gsnu200851


"richzip" wrote:

I have a spreadhseet that has about 800+ rows (2 columns), which takes
several pages. I am convering this sheet to PDF. Is there a way, within
Excel, that I can automaticaly split after a set number of rows, copy the
data and paste it to the top of the page in different columns? This way
.intead of 2 column x 800 rows (and 15 pages), I would have 8 columns x 200
rows (and 4 pages).