Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print in exlce using global and local variables? (Excel 2000)

    With the below code I print out several sections of a worksheet that each has its own text and graphs. Is there any way to apply unique headers to the specific ranges I am printing out? I want to keep the global page set up, but I want the header for each range to be "Range 1", "Range 2", etc... as it prints.


    Sub SEHSummary()
    With ThisWorkbook.Sheets("Sheet3")
    With .PageSetup
    .LeftFooter = "&D"
    .CenterFooter = ""
    .RightFooter = "Page &P"
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    .Range("$B$8:$G$62").PrintOut
    .Range("$B$67:$G$128").PrintOut
    .Range("$B$134:$G$214").PrintOut
    .Range("$B$221:$G$262").PrintOut
    .Range("$B$265:$G$321").PrintOut
    End With
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print in exlce using global and local variables? (Excel 2000)

    You could store the current LeftHeader, CenterHeader and RightHeader in variables, then set the desired header for each range before printing it, and finally restore the original header from the variables.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Print in exlce using global and local variables? (Excel 2000)

    If your ranges are all Named, you could put them into an Array, and loop through the Array like this:

    Dim lngN As Long
    Dim strDefault As String
    Dim varPrintRanges As Variant

    varPrintRanges = Array("Range1", "Range2", "Range3", "PrintMeLast")

    strDefault = Sheets("Sheet1").PageSetup.CenterHeader
    For lngN = 1 To UBound(varPrintRanges)
    Sheets("Sheet1").PageSetup.CenterHeader = varPrintRanges(lngN)
    On Error Resume Next
    Range(varPrintRanges(lngN)).PrintOut
    Next lngN
    Sheets("Sheet1").PageSetup.CenterHeader = strDefault
    Set varPrintRanges = Nothing
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print in exlce using global and local variables? (Excel 2000)

    Thanks! Here is the format I wound up with...


    Sub SESummary()

    With ThisWorkbook.Sheets("Sheet Name")
    With .PageSetup
    .LeftFooter = "&D"
    .CenterFooter = ""
    .RightFooter = "Page &P"
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .LeftHeader = "&48Title"
    .CenterHeader = ""
    End With

    .PageSetup.RightHeader = "&48SubTitle1"
    .Range("$i$8:$n$62").PrintOut

    .PageSetup.RightHeader = "&48SubTitle2"
    .Range("$i$67:$n$128").PrintOut

    End With

    End Sub

Posting Permissions

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