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.
--
HTH...

Jim Thomlinson


"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.