Printing non-adjacent areas on seperate sheets in one print job

[Dan Knight]

I've got a business modelling workbook that contains abt 25 worksheets
each with a large number (>40) of "tables" on any single worksheet
What I've doen so far is automate (via VBA) the printing of "reports
where non-adjacent named ranges are created and then selected an
printed. This gives me a single print job for each worksheet, with eac
table on it's own page.

However, the named ranges are giving me grief because of it 25
character limitation for the RefersTo property. I've done some playin
with the Union function to group named ranges together temporaily fo
printing purposes, but have not been too successful.

Does anyone know of a easier or more efficient way of accomplishin
what I'm trying to do. I'm thinking something possibly with the Area
property, etc.

Thanks

Dan Knigh

 

[Dave Peterson]

Not quite sure what problems you're having with the Union method. This worked
for me:

Option Explicit
Sub testme()
Dim myRng As Range
With ActiveSheet
Set myRng = Union(.Range("test1"), .Range("test2"), .Range("test3"))
End With
myRng.PrintOut preview:=True
End Sub

And if you're doing it by hand, you could define a bunch of names with long
"refers to", then make a "union" via Insert|Name|Define.

I could have used:
Insert|range|define
Names in workbook:
TestAll

Refers to:
=test1,test2,test3

You won't see it in the namebox dropdown, but you can type it in and select the
combined range.

And one more option. If the ranges don't change (or you can determine the
addresses someway), you could name them in your code.

with activesheet
.range("a1:b99,c12:d22,e1:f7").name = "test4"
end with

or you could just refer to that range and print it.

with activesheet
.range("a1:b99,c12:d22,e1:f7").printout preview:=true
end with