Print pages on sheet with values only

[Bob Hoecker]

I need to print pages only with value in specific columns. I don't know how to make a print code to say what i need to do. What i am looking to do is print a specific page only if there are values in these columns

k:9 - k:20
G:9-G-20
k :35 -K:46
g:35 - G:46
k:61 - K:72
g:61 - G:72
K:87 - K:98
G:97 - G:98
k:113 - K:124
G:113 - k:124
k:139 - K:150
g:139 - G:150
k:165 - k:176
g:165 -g:176
k:191-K:202
G:191-g:202
K:217-K:228
G:217-G:228
K:243-K:254
G:243-G:254
K:269-K:280
G:269-G:280
K:295-K:306
G:295-G:306
K:321-K:332
G:321-G:332
K:347-K:358
G:347-G:358
K:373-K:384
G:373-G:384
K:399-K:410
G:399-G:410

I also am unsure where to post the marco/vba code to preform such a task.
I also would provide an attachment but i was not allowed to. any help would be greatly appreciated
Thank you all for your help,​

 

[Ken Puls]

What I would probably do with this is set up a "Validation area" on this sheet or another sheet. (Doesn't matter which.) That are would use a =COUNTA(...) formula to check if anything had been entered in the cells. Once you've done this, then you'd sum up all the individual validation ranges. If it equals zero, your cells are blank, and therefore don't need to be printed. If not, then you can print it.

The advantage of this is that it will be calculated as the user enters data, and avoid any looping in the code. The check can be instantaneous.

As for where to place the code, that depends. Do you want to trigger this macro when the user tries to print, preventing them from printing if the cells are empty, or do you want to trigger it by pressing a button and only printing if this is data?

If the former, then you'd use the following macro, which goes in the ThisWorkbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.CodeName = "Sheet1" Then
        If ActiveSheet.Range("D6").Value = 0 Then
            Cancel = True
        Else
            'Must be data, so allow the worksheet to be printed
        End If
    End If
    
End Sub

If you'd rather trigger a manual check by clicking a button, or using the Run Dialog (Alt+F8), then use this code in a regular (standard) module:

Sub PrintSheet()
    If ActiveSheet.CodeName = "Sheet1" Then
        If ActiveSheet.Range("D6").Value <> 0 Then
            ActiveSheet.PrintOut copies:=1
        End If
    End If
End Sub

I'm not sure how familiar you are with coding. To open the visual basic editor, press Alt+F11. Then this article should help you find where to put it.

I've attached a sample workbook for you.

(PS, if you want to attach a file, just double-click the "Reply to Post" button. You can attach a workbook there.)

 

[Bob Hoecker]

Re:

I am still having issues with the print code. It is continuing to print all 16 pages and i only need the pages with any value in either of the cases column. I attached the workbook and it should be under the Addstop tab. Thank you again for your help with this. I am still slowly learning excel.

 

[Ken Puls]

Hi Bob,

My apologies, I understood that it was an all or nothing thing. Have a look at this.

I've set it up with a data table at the top, and to check each row in the table (a named range.)

 

[Bob Hoecker]

Re:

Thank you so much for your help Ken, I was unsure if this function was possible however, I do need the same formula on the signoff and manual stop tabs as well.

On the signoff tab, any value in the E column would be the pages needed to be printed.

As for the Manual stop tab, I needed the pages printed only with a value in the G catagory.

Your help is greatly appreciated, and is there a link on where i can learn to type these formulas in the future?

Best regards, Bob

 

[Ken Puls]

Hi Bob,

I've made some modifications to the code and also inserted some tables above the headers of the other sheets. Clicking that button should fire it all at once for you.

With regards to learning the formulas, this place is as good as any. Are you curious about the Offset one, CountA, or?

You'll notice in the other tables that I set up, I actually resorted to using SUM formulas instead. The Offset one worked fine in the initial worksheet because the route sheets had an identical number of rows. In the case of the other sheets, however, this was not the case, so we had to go and pick them off individually.

Let me know if you'd like any of these formulas explained more clearly.

Cheers,