Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro Error / Print Hidden Sheet (XL2003)

    I have a workbook with two pages.
    "Sheet3" - which is hidden, but contains the document which is to be printed from...
    "STARS Shift" - which has a macro button that prints the document on "Sheet3".

    This code works great (on the button) as long as "Sheet3" is not hidden. But when it is hidden, the macro button produces an error at the first line. Isn't there a way to print a range without having to first select it? Can a printout be produced from a hidden sheet? Guess I could add lines to the code that would unhide the sheet, print, and then re-hide the sheet...but that would cause a lot of screen flashing for the users. Advice much appreciated.


    Sub PrintSummarySheet()
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("STARS Shift").Select
    Range("G5").Select
    End Sub
    - Ricky

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

    Re: Macro Error / Print Hidden Sheet (XL2003)

    You don't need to select a sheet to print it, but you do need to make it visible (printing an invisible sheet makes no sense). You can set Application.ScreenUpdating to False to reduce screen flicker:

    Sub PrintSummarySheet()
    Application.ScreenUpdating = False
    With Worksheets("Sheet3")
    .Visible = True
    .PrintOut
    .Visible = False
    End With
    Application.ScreenUpdating = True
    End Sub

    Since the code doesn't select Sheet3, it isn't necessary to select the other sheet and a cell on it either.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank you

    The visible page is attractive, colorful, with live pie charts, etc. There is a macro button on the visible page that allows the user to produce a black & white printout of the data (a Summary Report without all the colors, etc.) The printout is built on an ugly sheet that has lots of unformatted data, formulas, a couple of lookup tables, and the like. It was simpler for me to just hide the sheet than it was to dress it up.

    I actually created the macro button from a "picturelink" to the document, so the user can view a live shot of the data that will be printed. I was able to reduce the image down to about a sixth of it's normal size, still legible, and fit nicely on the front sheet.

    The code you provided works perfect and there's no flicker at all on my machine. Can't even tell that it un-hides and re-hides the worksheet.
    Thank you very much.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

Posting Permissions

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