Formatting imported data

[Jonesl1]

I am having a simple issue in which I just can't figure out. I'm sure it's possible, but for the life of me can't seem to figure it out.

Here it is:
I have imported a list of data into an excel spreadsheet. This list is just a bunch of users names. When the data was imported, it placed the first entry in A1, the next in A2, etc. All the way up til A2000. Nothing was ever put into B$. Each field is just a username. The problem is when it comes to printing the document. I don't want to print a ton of pages with just a little data on each page. I would like to be able to print say A1 through A30 and then wrap A30 through A60 up into column's B1 through B30 and so on. Then once I have wrapped say 5 or 6 columns, to go onto the next page and begin the same sequence (hopefully this makes sense).


So for instance:

A
B
C
D
E
F

and I would like it to look like

A D
B E
C F

I know I could cut and paste each set, but there are 2000 and that would take a load of time to do. Is there some option that would do this for me?

Anyhow, this is causing a printing nightmare and I just need some help.

Thanks in advance!

 

[Atom]

I don't believe there is a way to do that with out manipulating the sheet it self

 

[Zack Barresse]

You can do it with code. As most sheets have 40-45 rows per sheet by default, you may want to do more than 30. Using something like the routine below, you can set it to whatever you want. Also, it will fit your page(s) to one tall, meaning it will make up the difference for any blank page areas.


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> PrintSetup_SingleColumn()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> MyArea <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Interval <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws = ActiveSheet <SPAN style="color:#007F00">'** set as desired</SPAN>
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    iCol = 2
    n = 0
    Interval = 40 <SPAN style="color:#007F00">'** set as desired</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> i = Interval <SPAN style="color:#00007F">To</SPAN> LastRow + Interval <SPAN style="color:#00007F">Step</SPAN> Interval
        ws.Range(Cells(i, 1), ws.Cells(i - (Interval - 1), 1)).Cut ws.Cells(1, iCol)
        iCol = iCol + 1
    <SPAN style="color:#00007F">Next</SPAN> i
    ws.Columns(1).Delete
    <SPAN style="color:#00007F">With</SPAN> ws.PageSetup
        .PrintArea = ws.Range("A1", ws.Cells(Interval, iCol - 3)).Address
        .FitToPagesTall = 1
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Does this help?

 

[Jonesl1]

Formatting Imported Data

Just wanted to say a big THANK YOU! That worked perfectly. Very much appreciated.