Printing 3 sheets in 1

[Rise]

I have a 400 line spreadsheet with 4 columns. At 75% zoom, this 8-pager becomes 6 pages. I can then fit the 4 columns 3x on a single print sheet and if I print front & back, it all fits on 1 page. My question is: is there an easier way to do this instead of manually cutting/pasting columns on the sheet to get the format I'm looking for? It will have to be a daily routine. I looked through the printing options and nothing immediately stuck out at me.

[Leif]

Put a formula in column E so that the cells = column A (e.g. '=A1'), in column F = column B etc, down as far as row 50.
Then repeat starting in cell I1 = A51, J1 = B1 etc.
Set the print area to = A1 to AF50 and you should end up a 2-page print if I've done my sums/read your question correctly!

[HansV]

You could create a second sheet and copy/paste special the blocks as links. You will only have to do this once.
When you edit the values in the original sheet, the second sheet will automatically reflect the changes.
Use the second sheet for printing.
See the attached workbook.

PrintSample.xlsx

[ChrisGreaves]

My question is: is there an easier way to do this instead of manually cutting/pasting columns on the sheet to get the format I'm looking for?

yes.
"Smart page zoomer for Excel. Works out the best zoom factor BEFORE you print."
http://www.chrisgreaves.com/Downloads/Downloads.htm
Email me if you run into problems.

[Rise]

3 responses within 3 minutes of each other, not bad!

I went with leif/hans' suggestion but I'll be sure to check out the smart page zoomer as well! Thanks guys.

[Rise]

ruh roh - after using this solution for a few days I realized there's a slight issue... as data get's moved around by another macro, it seems to change the cell call outs on the other sheet to correspond to where the data got moved to. this creates #REF errors... do I need to individually type each one out as =Inventory!$C$2, =Inventory!$C$3, etc? that wouldn't be very fun

[HansV]

You could use this macro to convert the formulas to use INDIRECT. This makes them refer to the physical cell addresses in the source sheet:

Code: Select all

Sub ConvertFormulas()
  Dim oCell As Range
  For Each oCell In ActiveSheet.UsedRange
    If oCell.HasFormula Then
      oCell.Formula = "=INDIRECT(""" & Mid(oCell.Formula, 2) & """)"
    End If
  Next oCell
End Sub

You have to do this before the formulas are messed up, of course.

I have attached the result of running the macro in the workbook from my previous reply.

PrintSample.xlsx

[Rise]

that seems to do the trick Hans. I didn't know of that formula indirect, thanks.