Results 1 to 6 of 6

Thread: Print pages on sheet with values only

  1. #1

    Print pages on sheet with values only



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

    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,

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:
    Code:
    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:
    Code:
    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.)
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    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.
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5

    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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,
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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