Problem formatting columns for print method

Theresa Ashdown

New Member
Joined
Mar 7, 2004
Messages
2
I have a spreadsheet that contains 6 columns and over 21,000 rows of data. A normal print will give me approx 350 pages of output. As the columns of data only fill half of the page width, what I would like to do is 'wrap' the information so that I get, for example, the first 60 rows of data printing on the left hand side of page 1, the second 60 rows of data printing on the right hand side of page 1, the third 60 rows of data printing on the left hand side of page 2....
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Theresa:

Welcome to MrExcel Board!

To my knowledge, for printout in the fashion you desire, you will have to rearrange your data as first 60 rows of 6 columns on left side of page 1, rows 61 through 120 in second set of 6 columns to the right side of page 1, and so on.

Would this approach work for you?
 

Theresa Ashdown

New Member
Joined
Mar 7, 2004
Messages
2
I was hoping that was a cleverer way to handle this rather than cutting and pasting through the length of the worksheet. Thanks anyway.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,229
Theresa - -

This tested fine for me based on what you want, except that I made it for 50 rows instead of 60, for no other reason than 60 rows of data were too small for me to squint at on a sheet late Sunday night (how's that for a scientific rationale).

Fifty is a more intuitive number anyway, which places 100 records neatly on a sheet. If you really want 60, then modify the code to change the 50's to 60, and the 49's to 59.

Finally, to keep things uncomplicated, I placed the line
v.PrintPreview
so you can see if the code works the way you want, without wasting a bunch of printed paper to test it. Just substitute that line with
v.PrintOut
if the macro passes muster.

Sub PrintColumnPairs()
With Application
.ScreenUpdating = False
Dim u As String, v As Worksheet, w As Long, x As Long, y As Long, z As Long
u = ActiveSheet.Name
Set v = Worksheets.Add
Sheets(u).Activate
z = Cells(Rows.Count, 1).End(xlUp).Row
y = 1: x = 1: w = 1
Do While y <= z
For w = 1 To 3 Step 2
Range(Cells(y, 1), Cells(y + 49, 2)).Copy
v.Paste v.Range(v.Cells(x, w), v.Cells(x + 49, w + 1))
y = y + 50
Next w
x = x + 50
v.Rows(x).PageBreak = xlManual
Loop
v.PrintPreview
.DisplayAlerts = False
v.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Theresa:

In addition to fine contribution from Tom Urtis, following simulation depicts a formula based approach ...

I have assumed my six column data is in Sheet13. I have created Sheet13p for PrintOut using the layout above.

There are two formulas ...

in cell A1 ... =INDIRECT("sheet13!"&CHAR(64+COLUMNS($A:A))&(INT(ROWS($A$1:$A1)/61))*60+ROWS($1:1))
this is then copied across to columns B through F and as far down as necessary.

in cell H1 ... =INDIRECT("sheet13!"&CHAR(64+COLUMNS($A:A))&(INT(ROWS($A$1:$A1)/61)+1)*60+ROWS($1:1))
this is then copied across to columns I through M and as far down as necessary.

I personally would prefer the macro based approach -- but here it is!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,934
Messages
5,627,697
Members
416,266
Latest member
stevenvanroeden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top