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("Pri ntArea")

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.Co unt)).Address
next Sht



"hurlbut777" wrote:

Having a small problem with setting up a dynamic print range. I have defined
a range by clicking insertnamedefine. 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").Cell s.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.