Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Location
    Florida
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Problem Re: Printing (2003)

    Hi,

    I'm new here and I hope to learn alot.

    Awhile back, somebody helped me write some code that prints out only the sheets in my workbook that have contents in a particular cell (in this case F20). This has been working perfectly until now (we got a new printer and I'm wondering if that's what's doing it. I doubt it since it gives me the error on all the printers now). It gives me an error that says "Run Time Error: 1004 Method Print Out of Object Worksheet failed" and asks if I want to debug after printing only a few sheets.

    Here is the code if anyone can help:

    Sub PrintSheets()
    '
    ' PrintSheets Macro
    ' Macro recorded 7/3/2003 by Stacey
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Dim s As Worksheet

    For Each s In ActiveWorkbook.Sheets
    If s.Cells(20, 6).Value <> 0 Then
    Debug.Print "F20 doesn't equal 0 on " & s.Name
    s.PrintOut Copies:=1
    End If
    Next
    End Sub

    The line that says "s.PrintOut Copies:=1" is highlighted if I say yes to debugging.

    Thanks!
    Stacey

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

    VBA Problem Re: Printing (2003)

    Welcome to Woody's Lounge!

    You'd have a problem if your workbook contained chart sheets, but it would lead to a type mismatch error. To be on the safe side, it would be better to replace ActiveWorkbook.Sheets with ActiveWorkbook.WorkSheets (Sheets includes both worksheets and chart sheets).

    Apart from that, the code looks OK. Perhaps it's a timing problem. You could try replacing Debug.Print with MsgBox temporarily, and wait a few seconds before clicking OK each time, to see if that prevents the problem.

  3. #3
    New Lounger
    Join Date
    Jul 2005
    Location
    Florida
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Problem Re: Printing (2003)

    I'll try that on Monday. Thanks.

    Is there an easier or better way to do this? Maybe without the VBA? The workbook is huge and I really only need to print sheets that have contents in F20.

    I'm always looking for other ways to do things.

    Stacey

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

    VBA Problem Re: Printing (2003)

    Not really.

    You can manually select and print multiple worksheets within a work book by using the technique of shift-clicking the tabs of contiguous series of sheets, or ctrl-clicking the tabs of noncontiguous sheets, then clicking the print icon. You would need to examine each sheet to see if it has something in cell F20 in order to know to select the sheet. If there's a lot of sheets, the macro is going to be faster.

    I'll see if I can tweak the code to work better (if lightning Hans doesn't beat me to it).
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    VBA Problem Re: Printing (2003)

    You could try the following macro. It selects the sheets for which F20 is non-zero, then prints the selected sheets:

    Sub SelectSheets()
    Dim s As Worksheet
    Dim n As Integer
    For Each s In ActiveWorkbook.Worksheets
    If s.Range("F20") <> 0 Then
    s.Select (n = 0)
    n = n + 1
    End If
    Next s
    ActiveWindow.SelectedSheets.PrintOut
    Set s = Nothing
    End Sub

  6. #6
    New Lounger
    Join Date
    Jul 2005
    Location
    Florida
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Problem Re: Printing (2003)

    Thanks. I'll give this a try on Monday morning. Hopefully, this will work.

    Thanks again,
    Stacey

Posting Permissions

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