Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: automating print area

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •