Split Data 1 Column to 3 columns, Excel 2000 & 2003

[jfcby]

Hello,

I have a spreatsheet with one column 10048 rows of data. I would like
to make 3 colums of data. This is how I would like to separate my
data: Page 1 with three colums, Page 2 with three columns, Page 3 with
3 colums, etc. Also, if possible my data needs to be separated after
each page ex: move page 2 column 1 to page 1 column 2, move page 3
column 1 to page 1, then move page 4 column 1 to page 2 column 1,
page 5 column 1 to page 2 column 2, page 6 column 1 to page 2 column
3, etc.

If the above cannot be accomplished. How can I split the data evenly
into three columns?

Thank you for your help,
jfcby

 

[Joel]

First : 10048 will never evenly divide into three column. One column will
have one more row of data than the other two.

Second: this can be done manually without programming.
Column A will have rows 1-3350
Column B - Cut rows 3351 to 6699 and paste in Column B Row 1.
Column C - Cut rows 6699 - 10048 and paste in column C row 1.

Now go to Print Preview. Data should be in three columns. If not, adjust
column widths to fit in three columns.

 

[Gord Dibben]

!-50 in column A
51-100 in column B
101-150 in column C

Repeating to end with a blank row or pagebreak between each set

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

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 1).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 1).Cut _
Destination:=Cells(iTarget, "B")
Cells(iSource + 100, "A").Resize(50, 1).Cut _
Destination:=Cells(iTarget, "C")
iSource = iSource + 150
iTarget = iTarget + 51
'change 51 to 50 if using the pagebreak line
'PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP