Print a worksheet if value in the cell

M

mgrroyall

New member
Joined
Feb 10, 2018
Messages
6
Reaction score
0
Points
0
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.
Untitled.jpg
 
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:
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?
 
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.
 
Actually, the sheets are named 4 to 22, 24 to 27, 29 to 33, 35 to 43 and 45 to 47.
 
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
 
that bit went through, now I got mismatch error in that line "Sheets(Split(Mid(prntShts, 2), "|")).PrintPreview".
 
the file
 

Attachments

  • Copy of working master copy12a.xlsm
    157 KB · Views: 6
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
 
that works perfectly, thanks a lot mate. the trees will be saved now.
 
You must log in or register to reply here.
Back
Top