x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 620

# How to make long Excel worksheet print across the page

I have an Excel worksheet that has 3 columns and about 900 rows. The total width of the 3 columns is only about 2 inches. When I print this it takes about 20 pages but there is a lot of wasted space on the right hand side of the pages. I would like to make the 3 columns repeat accross the page (like you can do with Word "columns" formatting) before jumping to the next page. If I can get 3 sets of columns to print on a page, I will only have about 7 pages to deal with instead of 20, and I would save paper too. COPYING AND PASTING IS NOT AN ACCEPTABLE SOLUTION. I want this to happen automatically, without the need for manual manipulation.
0
donander
• 7
• 7
• 6
• +4
1 Solution

Commented:
I've got a convoluted method, but I'm interested to see if anyone posts a real answer before I post it.  Watching for now...
0

East Coast ManagerCommented:
Though I know you don't want to copy and paste, you could put in (for instance) do this cell E1=a451, cell F1=b451, and cell G1=c451, and copy these three down about 450 rows.

And you could insert it as a linked object in Word, with word formatted for columns. That way, if you change, add, etc., it will automatically be changed/added in the Word file.
0

Author Commented:
Sorry, these answers are not acceptable.
0

Commented:
I have another solution if you would like the results to show up in order row by row (meaning the first 3 groups on row 1 and the next 3 on row 2 etc).

First I must say that this answer not will qualify for 200 points but if nobody comes up with a better solution you can probably reduce the points quite a bit.

Let's say you have your values in column A to C (3 columns)

In cell D1 write "=A1" (yes, it should be A1 since we will hide column A - C later)
Copy D1 two cells to the right (will produce "=B1" in E1 and "=C1" in F1)
In cell G1 write "=A2" and copy that cell two cells to the right ("=B2" and "=C2")
In cell J1 write "=A3" and copy that cell two cells to the right ("=B3" and "=C3")

Select this whole range and fill down (double click the little black square on the last cell).
Select column A - C and hide them.

As you can see the result is not quite finished yet but we will filter out the incorrect rows.

In cell M1 write "YES" and then write "No" in M2 & M3.
Select cells M1 - M3 and fill down (should be "Yes" in M4 and then two "No" before next "Yes" etc.)

Select cell M1 and insert an "Auto filter" (Menu "Data")
Select the value "Yes" in that filter
Set column D - L as printout area and you are ready to print.

It's not pretty, but will work and I would personally prefer to read the results row by row rather than column by column.

Johan
Sweden
0

Author Commented:
I am asking this for one of the users I support. In addition to the fact that it is not automatic, as I requested, I think my user would want to be the one who decides how to format her worksheet. Sorry, but this answer is not acceptable.
0

Commented:
Well, well, lookie here :-). There's another q that I'm in the process of answering at the moment that's the same as this: http://www1.experts-exchange.com/EQ.10339431

Here is the solution that the q'er is trying out at the moment:

it 's a bit long but it's a generic routine that you can use anywhere. I've written it for my benefit too as I haven't had such a routine before though
I 've always thought about it.

it 's intelligent enough to determine how many sets of data it can place across the page without overflowing onto another sheet. You therefore don't need to worry about this as it'll always fit as much on
as possible.

Similarly , it 's intelligent enough to know how many rows will fit on the page so that it can wrap the data correctly across the page. There may, however, be a problem if your row heights are not uniform
in the range that you want to print. I haven't tested that bit fully but it would be harder to create a generic routine for this - and the resulting macro would take longer to run too.

The first procedure below (YourPrintOut()) is the little sub that you will need to use to call the generic sub. You can then assign YourPrintOut() to a command button or menu item - which you would not
be able to do with the generic sub as it has arguments that need to be defined.

The generic procedure is commented throughout so it's easier for you to understand what's going on at each part. It should hopefully give you a bit of "good" code from which you can improve your
coding techniques.

If you have any questions, don't hesitate to yell.

Public Sub YourPrintOut()
PrintSideBySide ActiveSheet.Range("A1:D100"), 2, 1
End Sub

'rngSourceToPrint - This is the range that you want to print e.g. Selection
'      These correspond with the first row(s) in the rngSourceToPrint
'QtyColumnGap - How many columns you want in the gap between the side-by-side sets of data.
Public Sub PrintSideBySide(ByVal rngSourceToPrint As Range, ByVal QtyHeaderRows As Long, _
ByVal QtyColumnGap As Long)

Dim shtToPrint As Worksheet 'This is the sheet where the data will be placed side-by-side.
Dim shtSource As Worksheet 'The user's sheet that contains the data that is not side-by-side.

Dim rngForPrinting As Range 'The side-by-side range that we want to print.
Dim rngDestnOfNextCopy As Range 'The destination where we want to do the next copy.
Dim rngSourceOfNextCopy As Range 'The source range that we next want to copy.
Dim rngHeader As Range 'The range that is the header of the table

Dim lCount As Long 'A generic counter that we'll use for various purposes throughout the lifetime
'of this procedure.
Dim lDataSetAcrossPage As Long 'The count of the data set that we are placing across the page.

Dim lRowsPerPage As Long 'The number of rows that will fit on a page
Dim lColsPerPage As Long 'The number of columns that will fit on a page

On Error GoTo PrintSideBySide_Error

Application.ScreenUpdating = False

'Check the parameters that we have passed.
If QtyColumnGap <= 0 Then QtyColumnGap = 0

'First create a new sheet from which we shall perform the printout
Set shtSource = rngSourceToPrint.Parent
shtSource.Copy shtSource
Set shtToPrint = ActiveSheet

'Copy the range over that we want to print
rngSourceToPrint.Copy shtToPrint.Cells(1)
Set rngForPrinting = shtToPrint.UsedRange

'Ensure that the column widths are the same.
For lCount = 1 To rngSourceToPrint.Columns.Count
shtToPrint.Columns(lCount).ColumnWidth = rngSourceToPrint.Columns(lCount).ColumnWidth
Next lCount

'Set up the print headers - these will be required now as their addition changes
'how many rows we can display on each page.
End If

'We can now find how many rows that can fit on a page
'If the next statement is in error, then all of the data will fit on the one page anyway.
On Error Resume Next
lRowsPerPage = shtToPrint.HPageBreaks(1).Location.Row - 1 - QtyHeaderRows
On Error GoTo PrintSideBySide_Error

Select Case lRowsPerPage
Case Is <> 0
'Now we need to see how many sets we can place side-by-side on a page
With shtToPrint
'Setup our initial variables
Set rngDestnOfNextCopy = .Cells(1)
'We only need some dummy data to copy.
'However, I assume that each of the cells in the first row of the area to print contains data.
Set rngSourceOfNextCopy = rngForPrinting.Rows(1)
End With

lColsPerPage = 1
On Error Resume Next
Do Until shtToPrint.VPageBreaks(1).Location.Column < rngDestnOfNextCopy.Offset(0, _
rngForPrinting.Columns.Count).Column
'Setup the next range where we want to copy data to
Set rngDestnOfNextCopy = rngDestnOfNextCopy.Offset(0, rngForPrinting.Columns.Count + QtyColumnGap)

'Copy across the data
rngSourceOfNextCopy.Copy rngDestnOfNextCopy

'And again copy over the column widths
For lCount = 1 To rngSourceToPrint.Columns.Count
rngDestnOfNextCopy.Offset(0, lCount - 1).ColumnWidth = rngSourceToPrint.Columns(lCount).ColumnWidth
Next lCount

'And increment the counter of how many sets of data we can have side-by-side.
lColsPerPage = lColsPerPage + 1
Loop
On Error GoTo PrintSideBySide_Error

'Obviously, as we have now breached the vertical page break,
'we can only have one less amount of data column sets.
lColsPerPage = lColsPerPage - 1
Select Case lColsPerPage
Case Is <> 0
'We can now start the copy process for real.

'Delete the sheet we've been playing with
shtToPrint.Delete

'And create a new sheet object.
'This is necessary as the UsedRange property of the old sheet will have been "tarnished".
shtSource.Copy shtSource
Set shtToPrint = ActiveSheet
shtToPrint.Cells.ClearContents

'Ensure that the column widths are the same.
'And we want to do this for the amount of data sets that we are to fit side-by-side.
For lDataSetAcrossPage = 1 To lColsPerPage
For lCount = 1 To rngSourceToPrint.Columns.Count
shtToPrint.Columns(lCount + ((lDataSetAcrossPage - 1) * (rngSourceToPrint.Columns.Count + _
QtyColumnGap))).ColumnWidth = rngSourceToPrint.Columns(lCount).ColumnWidth
Next lCount
Next lDataSetAcrossPage

'Setup the initial range where we are to copy data from and to.
Set rngSourceOfNextCopy = rngSourceToPrint.Cells(1)
Set rngDestnOfNextCopy = shtToPrint.Cells(1)

'Copy over and set up the print headers, overwriting the destination range so that the headers are overwritten.
If Not (rngHeader Is Nothing) Then
For lDataSetAcrossPage = 1 To lColsPerPage
rngHeader.Copy shtToPrint.Cells(1).Offset(0, ((lDataSetAcrossPage - 1) * _
(rngSourceToPrint.Columns.Count + QtyColumnGap)))
Next lDataSetAcrossPage

End If

'Now start copying the data
lDataSetAcrossPage = 1
Do Until Application.Intersect(rngSourceOfNextCopy, rngSourceToPrint) Is Nothing
rngSourceOfNextCopy.Resize(lRowsPerPage, rngSourceToPrint.Columns.Count).Copy
'We don't want to copy formulae across in case some of the cell references are not applicable.
rngDestnOfNextCopy.PasteSpecial xlPasteValues
rngDestnOfNextCopy.PasteSpecial xlPasteFormats

'Determine which data set we have just copied across
'and where the next range is to be copied to
Select Case lDataSetAcrossPage
Case lColsPerPage
lDataSetAcrossPage = 1
Set rngDestnOfNextCopy = rngDestnOfNextCopy.Offset(lRowsPerPage, 0).EntireRow.Cells(1)
Case Else
lDataSetAcrossPage = lDataSetAcrossPage + 1
Set rngDestnOfNextCopy = rngDestnOfNextCopy.Offset(0, _
rngSourceToPrint.Columns.Count + QtyColumnGap)
End Select

'Determine the next range that we want to copy across
Set rngSourceOfNextCopy = rngSourceOfNextCopy.Offset(lRowsPerPage, 0)
Loop

Case Else
MsgBox "There isn't enough space to put any columns side-by-side." & vbCr & _
"This is because your data spans more than one page across anyway." & vbCr & _
"You may have to change the Page Setup before trying again.", _
vbExclamation + vbOKOnly, "Impossible to put side-by-side"
End Select
Case Else
'All of the data will fit on the one page anyway.
End Select

Set rngForPrinting = shtToPrint.UsedRange
rngForPrinting.PrintOut

PrintSideBySide_Exit:
On Error Resume Next
'Clear up our work area
Set shtSource = Nothing
shtToPrint.Delete
Set shtToPrint = Nothing

Set rngForPrinting = Nothing
Set rngDestnOfNextCopy = Nothing
Set rngSourceOfNextCopy = Nothing

Application.ScreenUpdating = True

Exit Sub

PrintSideBySide_Error:
'    Stop: Resume
MsgBox "An error was encountered during the PrintSideBySide() procedure"
Resume PrintSideBySide_Exit
End Sub

0

Commented:
Has been hard to reconnect to EE after developping this little macro!!!

Here is my suggestion (VBA macro) :
- create a temp sheet
- columns ABC, EFG, IJK are set to width of original ABC
- I guessed A1:C1 were headings, so copied to top of blocks
- count rows of original table, divide by 3 and round
- copy first block to ABC, second to EFG, third to IJK
- print
- delete temp sheet

Example :

Sub Prt3Groups()
Dim ws As Worksheet
Dim prt As Worksheet

Set ws = ThisWorkbook.ActiveSheet 'the worksheet you was on when launching macro
Set prt = ThisWorkbook.ActiveSheet 'a new sheet that will format your print

'column A, E, I of prt will have same width as ws' column A
'       B, F, J                                           B
'       C, G, K                                           C
prt.Columns("A:A").ColumnWidth = ws.Columns("A:A").ColumnWidth
prt.Columns("B:B").ColumnWidth = ws.Columns("B:B").ColumnWidth
prt.Columns("C:C").ColumnWidth = ws.Columns("C:C").ColumnWidth

prt.Columns("E:E").ColumnWidth = ws.Columns("A:A").ColumnWidth
prt.Columns("F:F").ColumnWidth = ws.Columns("B:B").ColumnWidth
prt.Columns("G:G").ColumnWidth = ws.Columns("C:C").ColumnWidth

prt.Columns("I:I").ColumnWidth = ws.Columns("A:A").ColumnWidth
prt.Columns("J:J").ColumnWidth = ws.Columns("B:B").ColumnWidth
prt.Columns("K:K").ColumnWidth = ws.Columns("C:C").ColumnWidth

' I guess A1:C1 are headings and you need to copy E1:G1 and I1:K1

prt.Range("A1").Value = ws.Range("A1").Value
prt.Range("B1").Value = ws.Range("B1").Value
prt.Range("C1").Value = ws.Range("C1").Value

prt.Range("E1").Value = ws.Range("A1").Value
prt.Range("F1").Value = ws.Range("B1").Value
prt.Range("G1").Value = ws.Range("C1").Value

prt.Range("I1").Value = ws.Range("A1").Value
prt.Range("J1").Value = ws.Range("B1").Value
prt.Range("K1").Value = ws.Range("C1").Value

'calculate the number of rows you'll need on each group
Rowscount = Int(ws.Range("A1").CurrentRegion.Rows.Count / 3) + 1

'copy/paste groups

ws.Activate
Range("A2").Resize(Rowscount, 3).Select
Selection.Copy
prt.Activate
Range("A2").Select
ActiveSheet.Paste

ws.Activate
Range("A2").Offset(rowOffset:=Rowscount, columnOffset:=0) _
.Resize(Rowscount, 3).Select
Selection.Copy
prt.Activate
Range("E2").Select
ActiveSheet.Paste

ws.Activate
Range("A2").Offset(rowOffset:=2 * Rowscount, columnOffset:=0) _
.Resize(Rowscount, 3).Select
Selection.Copy
prt.Activate
Range("I2").Select
ActiveSheet.Paste

'Print

prt.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'delete the working sheet
prt.Delete

End Sub
0

Commented:
Donander: "I am asking this for one of the users I support. In addition to the fact that it is not automatic, as I requested, I think my user would want to be the one who decides how to format her worksheet. Sorry, but this answer is not acceptable."

If you are waiting for an answer that would mean that you or the users have to do nothing by your/themselves I don't understand why you offer so many points.

I don't see any other solution than that they use either a function (my or Dreamboat's example) or a macro (shown by Noggy and DFI) to solve their problem.

Nomatter what method they use they will only have to do it once (unless they decide to erase the workbook with the functions or macro's).

If you were responsible for supporting me with help in questions like these I wouldn't only want you to come up with alternative solutions, I would expect you to !
The final decicion will perhaps not be easier but most likley better for the end user if he has alternatives to choose from.  My solution was an alternative to Dreamboat's solution (it's called progress you know) and the difference between them is that my solution really is automatic in the sense that you can use it over and over again nomatter how many rows there are on the original list. You can vary the amount of columns quite easy also.

Maybee your users prefer to read from page 1 to 7 and then from page 1 to 7 two times more but I don't see the harm in suggesting them an alternative solution (just reading from page 1 to 7 ONCE).

Johan

0

Commented:
Donander: "I am asking this for one of the users I support. In addition to the fact that it is not automatic, as I requested, I think my user would want to be the one who decides how to format her worksheet. Sorry, but this answer is not acceptable."

If you are waiting for an answer that would mean that you or the users have to do nothing by your/themselves I don't understand why you offer so many points.

I don't see any other solution than that they use either a function (my or Dreamboat's example) or a macro (shown by Noggy and DFI) to solve their problem.

No matter what method they use they will only have to do it once (unless they decide to erase the workbook with the functions or macro's).

If you were responsible for supporting me with help in questions like these I wouldn't only want you to come up with alternative solutions, I would expect you to!
The final decision will perhaps not be easier but most likely better for the end user if he has alternatives to choose from.  My solution was an alternative to Dreamboat's solution (it's called progress you know) and the difference between them is that my solution really is automatic in the sense that you can use it over and over again no matter how many rows there are on the original list. You can vary the amount of columns quite easy also.

Maybe your users prefer to read from page 1 to 7 and then from page 1 to 7 two times more but I don't see the harm in suggesting them an alternative solution (just reading from page 1 to 7 ONCE).

Johan

0

Author Commented:
I was expecting an answer that involved some setting in page or print setup that we had overlooked. Since it is becoming apparent that answer doesn't exist, I am deleting the question. BTW, I offered so many points because I hoped it would attract a higher level of "expert."
0

Author Commented:
This question has a deletion request Pending
0

Commented:
This question no longer is pending deletion
0

Commented:
Donander: "BTW, I offered so many points because I hoped it would attract a higher level of "expert."

This is where you surprise me.  If there was a simple answer to your question (like a setting in print setup that you had overlooked) it would NOT be worth all those points and would not need any expert skills at all.
That's why I wrote that your points surprised me since you seemed to be looking for a simple answer and NOT an expert SOLUTION.

Any old fool that once heard of the function could collect your points (IF there was such a function!) but I can assure you that any old fool couldn't provide you with the code examples that Noggi and DFI came up with.

When your user has a problem I guess they want a solution to the problem, right ?  You have many solutions (probably the only) above but no, you will not accept the solution of the problem because it's not so simple and plain that you hoped it would be.  I suppose you will tell the user now that there is NO solution to their problem? You will of course not keep the code either.

I'm glad that I don't depend on you for my support, and I hope you don't count on mine in the future.  ( Well, not before tomorrow anyway.  ;-) )

Johan

Johan
0

Commented:
What caused this :  "This question no longer is pending deletion"

My comment below that ?  ( I only wrote one comment )
0

Author Commented:
I'll try one more time.
0

Author Commented:
This question has a deletion request Pending
0

Commented:
This question no longer is pending deletion
0

Commented:
Pending deletion?

So you decide the macro I spend time just for you is NOT AN ACCEPTABLE ANSWER, just because Bill did not write the code himself?

Can anyone tell me if EE has a black list somewhere?

d:o(
0

Commented:
Donander,

For good comprehension, I re-paste your question specification:

>COPYING AND PASTING IS NOT AN ACCEPTABLE SOLUTION. I want this to happen automatically, without the need for manual manipulation.

Both Noggy and DFI have provided you with an full-automatic solution, without the need for manual manipulation. It's not their fault that MS has limited the Excel Print Settings as this.

If you were looking for the Top Experts attention, I think you had their attention. Okki, Dreamboat and DFI may not be listed in the top 15 yet, but will be soon, and their contributions here are among the best I've seen.

Noggy is outstanding regarding code generation, and his macros are about the most complete and founded I've seen on this site.

If you look at the rankings to define top experts, be assured that Ture, Cri and me have all visited this question (I was writing a solution similar to Noggy's, but he was quicker), but I'm sure we all saw that you were already helped as good as possible.

I can understand and support the reaction from DFI and Okki, who put an important amount of time on this problem, just to hear from you that you don't like MS Excel Print Settings. Don't forget that all contributions here are on a voluntary base, there is no payment at all, all us experts can gain here is knowledge and some expert points, good for nothing but prestige.

Calacuccia
0

Author Commented:
Bye.
0

Commented:
Cheers for the support, Cal. I've been out the last few nights so haven't seen the comments on this q.

Trouble is that my "outstanding macros" never seem to get accepted as the answer :-( . As you say, they are "complete and founded" but it seems that no one wants code that they can just paste in and use anywhere.

At least I'm in the Top 10 now. About as high as I'll get without being on EE 25 hours a day :-).

donander - Please heed the comments of he other Experts above. We don't like getting pissed around - and you don't want to burn your bridges...not unless you want to try another profession.
0

Commented:
Hi there Noggy, nice to see you back.  For a while I was wondering if I was the only one that didn't appreciate Donander's way of expressing himself.

Anyway, we have all said what we think now and I think we should put this behind us now and give the guy a break.  After all, he might just have had a bad day (working with support isn't allways so fun).

Maybe if you ask DFI nicely he will share some points with you (but I'm sure you'll manage to stay ahead of us even without them).

BTW.  I like code that I can paste and use anywhere and since I'm new to VBA I really like to see different approaches to the same problem.

To help people with problems you don't have to be a VBA professional cause if I produce a different angel to a solution that nobody has though of I can count on the professionals (like Cal) to wake up and produce the code I can't get to work myself (yet).

Johan
0

Commented:
I've been busy the last few days, Okki. I'm configuring my new PC.....lots of swapping of Netscape stuff to IE stuff - macros all over the place :-) .
0

Commented:
Noggy,

Did not test your code but...
Sharing half the points there :

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10350439

d;o)
0

Commented:
Noggy,

Did not test your code but...
Sharing half the points there :

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10350439

d;o)
0

Commented:
Double posted comment, but you'll get once!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.