I'll try the code to see how it works but I'm not really good with VBA so I
might just copy and paste it for now. When I get better at VBA I will
revisit this issue and see if I can get it to work. Thanks so much for your
help.



"Billy Liddel" wrote:

You can try this macro. It splits up the data into four parts and copies it
into adjacent columns so make sure that it does not overwrite any data. You
can change the number of parts to split the data when you are prompted at the
start. The original list is left in place so you can check the results.

Option Explicit

Sub Splitto4()
Dim LNrows As Long
Dim iSplitRows As Integer
Dim iDestRows As Integer
Dim iCols As Integer
Dim iDestCol As Integer
Dim N2Split As Variant
Dim x() As Variant
Dim iIndex As Integer
Dim lStartRow As Long

N2Split = InputBox("Enter the number of Lists To Copy", _
"Split Data", 4, 100, 100)
LNrows = Range("A1").CurrentRegion.Rows.Count
iSplitRows = LNrows \ N2Split + 1
iDestRows = iSplitRows
lStartRow = 2
iCols = 2
iDestCol = iCols + 2

For iIndex = 1 To 4

x = Range(Cells(lStartRow, 1), Cells(iSplitRows, 2))
Range(Cells(2, iDestCol), Cells(iDestRows, iDestCol + 1)) = x
iDestCol = iDestCol + 2
lStartRow = iSplitRows + 1
iSplitRows = iSplitRows + LNrows \ N2Split + 1
Next iIndex
End Sub


If you have not used a macro before, Press ALT + F11, Choose Insert, Module
then copy the code into the module.

Close the VB Editor then with the sheet containg the data Press ALT + F8,
Click the macro name and click Run.

Regards
Peter Atherton

"JICDB" wrote:

I'm hoping someone can save me alot of work and aggrevation. I have a list
of cities in alphabetical order in column A and in column B a list of the bus
route numbers that serve those cities. This list is 20 pages long. If I
show it the way it is there is plenty of white space with lots of trees being
killed in the process. I want the data to show in two general columns but It
must remain in alphabetical order by city. Sort of like Word when you put
the data in columns. I want to use Excel because the remainder of the
booklet is in Excel.

Other than cutting and pasting the data at the page breaks and moving the
data around manually is there some way to do this?