Combining Print Areas on a Single Page

[Jane]

Hi,

I've used the "Add to Print Area" function to add an
isolated area to my print range. However, it prints it
on another page. Is there a way to combine these on a
single page?

Jane

 

[Ron de Bruin]

One way to print your selection with more areas on
one Page.

It will add a sheet and copy the selection areas on it
And delete that sheet after printing

Sub test()
Dim destrange As Range
Dim smallrng As Range
Dim newsh As Worksheet
Dim Ash As Worksheet
Set Ash = ActiveSheet
Set newsh = Worksheets.Add
Ash.Select

Set destrange = newsh.Cells(LastRow(newsh) + 1, 1)

For Each smallrng In Selection.Areas
smallrng.Copy
destrange.PasteSpecial xlPasteValues
Set destrange = newsh.Cells(LastRow(newsh) + 1, 1)
Next smallrng
newsh.PrintOut
Application.DisplayAlerts = False
newsh.Delete
Application.DisplayAlerts = True
End Sub

Public Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[Jane]

Hi Ron,

I'm sorry, but I have no idea how to use this. I'm
presuming it is VBA, and I have seen the Visual Basic
editor, but have no idea how to use it. I would
appreciate any help you (or anyone else) can provide.

Regards,

Jane

 

[Dave Peterson]

Is there anyway you could just hide the rows/columns you don't want to print,
then print the visible stuff?

If no, then David McRitchie has some notes about getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Select all your range (click on the first range and ctrl-click on subsequent)
Hit Tools|Macro|macros and click on test (rename that to something nicer!) and
see what happens.

========================
If you only have to do this once (or twice), you could just copy each area to a
new worksheet (in a nice contiguous fashion), then print that new worksheet.

You could do this, too:

Insert a new worksheet (you're gonna use this for printing).
Do this for each area in your range.
select the range
Edit|copy
go to the new worksheet and Shift-click-Edit|Paste Picture Link.

go back and do the rest of the areas (copy|shift-click-Edit|paste picture Link).

What's neat about the picture is that if you change the original, the picture
will change, too (values and formatting!).

You could keep that new worksheet forever--just for printing--but if the ranges
change, you'll have a little work to do.