Printing with excel?

[pgrmdave]

So, I've got a pivot table with names on the left and the jobs to which they charged hours along the top. This gets sorted then by department, and printed out for review. However, there are a lot of different jobs, and some departments only have a few people, which means that there ends up being either multiple pages with just a little information at the top, or one page with the stuff crammed so small at the top as to be unreadable. Is there any simple way to tell excel to use each printed page as two or three pages?

[Pyrotex]

As far as I know, there is no way to tell Excel to do this. :confused:

 

However, you can set up another worksheet, and in it you can set up equation fields to act as mirrors.

 

An equation field is where you define an equation in the upper left corner cell of an arbitrary rectangular region -- and then "copy/paste" that cell to all other cells in that region.

 

A mirror is a cell that contains the simple equation, =A1

where "A1" can be any other cell address.

It just mirrors, echoes or repeats the value of the other cell.

 

So, your first mirror field would be just one page wide and would echo just the 1st page's worth of your data.

 

Under that, your second mirror field would be just one page wide and would echo the 2nd page's worth.

 

And so on.

[pgrmdave]

Well, it took me two days, but I finally did it. The problem was that my data was not necessarily the same size every time, sometimes it was 13 rows and 10 columns, sometimes it was 5 rows and 40 columns. But, here's what I came up with

 

Here's my set up - in A1 I have a MATCH() function that checks the original data and finds the "Grand Total" cell, so that I know how many rows I have. Then in B1 I enter the number of columns I want across the page (the remainder of row 1 checks B1, and if it is the right column, sets itself to "end", if it is beyond that it sets to a space).

 

In the remainder of column A, I use this:

 

FLOOR(ROW()/$A$1,1)*(MATCH("end",$1:$1,0)-3)-1

 

So I get a list of numbers that describe the row offset (i.e. the first X are -1, the next X are 10, the next X are 21...this changes with A1, which determines how many rows are in the original data).

 

Then the data part (from cell C5):

=IF(C$1="",IF(INDEX(Table!$1:$65536,MOD(ROW(),$A$1),COLUMN()+$A5)="","",INDEX(Table!$1:$65536,MOD(ROW(),$A$1),COLUMN()+$A5)),"")

So, first it checks to see if row 1 is blank (it will be "end" or " " otherwise). If it is blank, then it checks the original data to see if it's blank (it was showing up as "0" unless I set it to ""). If it's not blank, then it pastes the original data.

 

I also set up a user event when the Number of Columns to be displayed is changed:

 

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$B$1" Then
       Columns("A:AE").Select
       Columns("A:AE").EntireColumn.AutoFit
       Range("B1").Select
       For x = Target.Value + 1 To 31
            Columns(x).ColumnWidth = 0
       Next x
   End If
   
End Sub

 

All in all far too much work for something that Excel should be able to handle itself.

[Bob Walsh]

Printing in Excel is a bit different than printing in some other programs, such as a word processor. One of the main differences is that Excel has five locations in the program that contain print-related options.The first, the Print button, is located on the standard toolbar. The other four of these are listed under the File menu: Print Preview,Page Setup,Print Area,

Print .