Rearranging aTable
 

I created a worksheet consisting of two relatively narrow columns. But it's
about 7,000 rows deep. I'd like to rearrange it so I'd have five sets of
column pairs on a single page, so I could print it using one fifth the
paper.

Assuming there are 40 lines per page, I'd then want page 1 to hold items
1-40 in the Columns A and B, then items 41-80 in rows C and D, etc.. Is
there a simple command to do this? if not is there a simple macro?
--
PT

Rearranging aTable
 

http://www.mvps.org/dmcritchie/excel/snakecol.htm

--
Kind regards,

Niek Otten

"PT" wrote in message
...
I created a worksheet consisting of two relatively narrow columns. But
it's
about 7,000 rows deep. I'd like to rearrange it so I'd have five sets of
column pairs on a single page, so I could print it using one fifth the
paper.

Assuming there are 40 lines per page, I'd then want page 1 to hold items
1-40 in the Columns A and B, then items 41-80 in rows C and D, etc.. Is
there a simple command to do this? if not is there a simple macro?
--
PT

Rearranging aTable
 

PT

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(40, 2).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 40, "A").Resize(40, 2).Cut _
Destination:=Cells(iTarget, "C")
Cells(iSource + 80, "A").Resize(40, 2).Cut _
Destination:=Cells(iTarget, "E")
Cells(iSource + 120, "A").Resize(40, 2).Cut _
Destination:=Cells(iTarget, "G")
Cells(iSource + 160, "A").Resize(40, 2).Cut _
Destination:=Cells(iTarget, "I")
iSource = iSource + 200
iTarget = iTarget + 41
Loop Until IsEmpty(Cells(iSource, "A"))

End Sub

Suggest you first make a copy of the sheet.


Gord Dibben Excel MVP

On Sun, 4 Dec 2005 20:17:54 -0800, "PT" wrote:

I created a worksheet consisting of two relatively narrow columns. But it's
about 7,000 rows deep. I'd like to rearrange it so I'd have five sets of
column pairs on a single page, so I could print it using one fifth the
paper.

Assuming there are 40 lines per page, I'd then want page 1 to hold items
1-40 in the Columns A and B, then items 41-80 in rows C and D, etc.. Is
there a simple command to do this? if not is there a simple macro?