Rearranging aTable

  • Thread starter Thread starter PT
  • Start date Start date
P

PT

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

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
 
Back
Top