automating print area

[swagman]

I am creating a template report that will sometimes have 40 pages of data and other times have 1 page of data. I have 40 pages worth of formulas in my template to account for the extemely large report senario, but now I also print 39 pages with only headers and footers on the small reports. Is there any way to define the print area using a formula or a cell reference?

 

[Cosmos75]

You can select the ranges and define then.

Select your range.
Go to Insert, Name, Define.
Name your range.

If the ranges change constantly, you may
need to do some research in Dynamic Ranges.

Here's a macro that makes all the worksheets print to one page each. The range is A1:H?, where H?? is the first cell with data (or formulas) in it starting from the bottom. Not quite sure this is what you are trying for though...

Sub FitToOnePage()

For Each ws In Worksheets
ws.Select
ActiveSheet.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
'Fits to one page wide and one page tall
.FitToPagesWide = 1
.FitToPagesTall = 1
End With


Next ws
End Sub
This message was edited by Cosmos75 on 2002-04-10 14:29