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

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

--
Kind regards,

Niek Otten

"PT" <[email protected]> wrote in message
news:eYo0YLV%[email protected]...
>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
>
>

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" <[email protected]> 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?