Results 1 to 10 of 10

Thread: Print a worksheet if value in the cell

  1. #1

    Print a worksheet if value in the cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,

    I got a problem that is above my excel knowledge. I want to save some paper and toner when I print the workbook and I would like to print only the worksheets that have a value bigger than 0 in a cell B6 on every worksheet 4 to 47. Those worksheets are populated with the data from the RS worksheet and B6 cell correspond to column B in RS worksheet. I usually print RS worksheet in a number of copies "print active sheets" then I print 1 copy of worksheets 4 to 47 " print entire workbook pages 5 to 44" as on some of them the data is not populated I get a page with table that I basically throw away. Is there anyway that I can do it without counting the pages that are populated and printing in parts?? Thanks in advance for your help.
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	13 
Size:	99.0 KB 
ID:	7770

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    658
    Articles
    0
    does this macro help any ?
    Code:
    Sub WhatToPrint()
    
    Dim i As Integer, ws As Worksheet, prntShts As String
    
    For i = 4 To 47
        Set ws = Sheets(CStr(i))
        If ws.Range("B6") > 0 Then
            prntShts = prntShts & "|" & ws.Name
        End If
    Next i
    
    Sheets(Split(Mid(prntShts, 2), "|")).PrintPreview
        
    End Sub
    Last edited by NoS; 2018-02-10 at 07:31 PM.

  3. #3
    Hi mate,

    I tried but it seems there is an issue with line Set ws = Sheets(CStr(i)). It says it is out of range?

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    658
    Articles
    0
    Setting up a test workbook according to the picture you posted it works.

    My guess is you either don't have all those sheets or they aren't named as the picture shows.
    Maybe leading or trailing spaces ?

    You could attached a sample workbook so everybody's dealing with the same thing.

  5. #5
    Actually, the sheets are named 4 to 22, 24 to 27, 29 to 33, 35 to 43 and 45 to 47.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    658
    Articles
    0
    Try this
    Code:
    Sub WhatToPrint_2()
        
        Dim i As Integer, ws As Worksheet, prntShts As String
    
    For i = 4 To 47
        
        On Error Resume Next
        Set ws = Sheets(CStr(i))
        On Error GoTo 0
        
        If Not ws Is Nothing Then
            If ws.Range("B6") > 0 Then
                prntShts = prntShts & "|" & ws.Name
            End If
        End If
    Next i
    
    Sheets(Split(Mid(prntShts, 2), "|")).PrintPreview
        
    End Sub

  7. #7
    that bit went through, now I got mismatch error in that line "Sheets(Split(Mid(prntShts, 2), "|")).PrintPreview".

  8. #8
    the file
    Attached Files Attached Files

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    658
    Articles
    0
    Sorry, didn't check that there actually are sheets to be printed.
    change
    Code:
    Sheets(Split(Mid(prntShts, 2), "|")).PrintPreview
    to
    Code:
    If Len(prntShts) > 0 Then
        Sheets(Split(Mid(prntShts, 2), "|")).PrintPreview
    Else
        MsgBox "No sheets to be printed."
    End If

  10. #10
    that works perfectly, thanks a lot mate. the trees will be saved now.

Posting Permissions

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