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

    Print Macros (Excel 2000)

    I have a print macro that reads the following:

    Sub SEHSummary()
    '
    With ActiveSheet.PageSetup

    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .Order = xlDownThenOver
    .FitToPagesWide = 1
    .FitToPagesTall = 1


    Range("B8:G62").Select
    Range("B62").Activate
    ActiveSheet.PageSetup.PrintArea = "$B$8:$G$62"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    Range("B67:G128").Select
    Range("B128").Activate
    ActiveSheet.PageSetup.PrintArea = "$B$67:$G$128"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    Range("B134:G214").Select
    Range("B214").Activate
    ActiveSheet.PageSetup.PrintArea = "$B$134:$G$214"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    Range("B221:G262").Select
    Range("B262").Activate
    ActiveSheet.PageSetup.PrintArea = "$B$221:$G$262"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    Range("B265:G321").Select
    Range("B321").Activate
    ActiveSheet.PageSetup.PrintArea = "$B$265:$G$321"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


    End With

    End Sub

    This sets up my page, my range, and puts several charts and cell text fields neatly onto one Legal sized piece of paper. Can you tell me how to alter this code to direct it to print a specific sheet (say, sheet 3) even if its not the active page?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print Macros (Excel 2000)

    Does this do what you want?

    Sub SEHSummary()
    '
    With sheets("Sheet3").PageSetup

    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .Order = xlDownThenOver
    .FitToPagesWide = 1
    .FitToPagesTall = 1


    .PrintArea = "$B$8:$G$62"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    .PrintArea = "$B$67:$G$128"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    .PrintArea = "$B$134:$G$214"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    .PrintArea = "$B$221:$G$262"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    .PrintArea = "$B$265:$G$321"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


    End With

    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Macros (Excel 2000)

    Does the following line at the beginning of the macro do what you want?

    <code>
    WorkSheets("Sheet3").Activate
    </code>
    Legare Coleman

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

    Re: Print Macros (Excel 2000)

    Neither one worked. I wish I could send it, but its too big.

    Subscript out of range.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print Macros (Excel 2000)

    what do you mean by it doesn't work?

    Do you get errors or does it do something else?

    Steve

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

    Re: Print Macros (Excel 2000)

    I just added the error to the post above: Subscript out of range when I debug it.

  7. #7
    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 Macros (Excel 2000)

    Which line errors out?
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Print Macros (Excel 2000)

    Line 9:


    and highlights

    With Sheets("Sheet3").PageSetup

  9. #9
    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 Macros (Excel 2000)

    And the Active workbook has a sheet called "Sheet3"? Legare's and Steve's macros should both work, try this:

    Sub SEHSummary()
    With ThisWorkbook.Sheets("Sheet3")
    With .PageSetup
    .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
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Print Macros (Excel 2000)

    This might be a stupid question, but the actual object name is Sheet3(OE). "OE" is the tab name. Does it need that entire piece?

    I got the same error with the new code as well.

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

    Re: Print Macros (Excel 2000)

    Eep! That was it. I changed the "Sheet3" to "OE". I thought it required the sheet #, not the text. Thanks fo ryour patience!

  12. #12
    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 Macros (Excel 2000)

    Yes, since the sheet name reference is in quotes, it needs to be the Name of the object, which in this case is "OE" - Sheet3 is its CodeName which is the internal object name - see the VB help for CodeName. Name and Codename do not have to be the same.

    With ThisWorkbook.Sheets("OE") ... should work.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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