Dynanic Print Range

[hurlbut777]

Having a small problem with setting up a dynamic print range. I have defined
a range by clicking insert>name>define. Named range is PrintArea and below
is the formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

I have also inserted the following vba code into the workbook:

Private Sub Workbook_BeforePrint (Cancel As Boolean)
With ActiveSheet
..PageSetup.PrintArea = .Range(.Range("A1"),
..Range("PrintArea").Cells(.Range("PrintArea").Cells.Count)).Address


This setup does exactly what I need with a workbook with only 1 worksheet.
However, I need help on adjusting this to work with a workbook with multiple
worksheets. Can the dynamic range be defined in vba using ActiveSheet, and
if so, what would that look like? I'm looking for something other than a
dynamic print range for every worksheet.

 

[Jim Thomlinson]

So long as you are only using the english version of XL you do not need the
code. XL stores the print area as a locally defined named range called
Print_Area. If your dynamic named range was changed to include the under
score in the name and declared local to the sheet then you would get a
dynamic print area. IYou would still need to create a dynamic named range
formula for each sheet byt that is generally not too bad to do. You could
even do it via code if there were enough sheets to warrant the effort.

 

[Joel]

Have you defined Name (PrintArea) include the Sht Name like code below. You
don't need the period in front of the named Range unless you have more than
one workbook opened.

either
Range("PrintArea")
or
workbooks("Book1.xls").Sheets("Sheet1").Range("PrintArea")

A Named Range is a weird item. It is a worksheet object but is really a
workbook object. If you look at the named ranges on the worksheet menu
Insert - Names you see the Sheetname but the name only returns the range
without the sheet. You can only get the sheet name by using RefersTo.

Sheet1PArea
Sheet2PArea
Sheet3PArea

The in your code use the sheet name to get the area for each sheet

for each sht in sheets
PrintArea = Sht.Name & "PArea"
.PageSetup.PrintArea = Range(.Range("A1"), _
Range(PrintRange).Cells(Range(PriontArea).Cells.Count)).Address
next Sht