Print Macro Help - Is it possible to Macro so only the pages in a sheet that have data in certain cells are printed?

Barryoffshore · Jul 21, 2014

I have a workbook that has 87 sheets, and each sheet has 21 printable pages. Is there a macro which will print only the pages that have entries on them? For instance, if on a single sheet, there is data in cells E7, E37, E66 & E95, only the pages that have those cells in them will print. Is a macro like this possible and if so, can someone help me with it please???

Thank you!!!
Bear

Michael M · Jul 21, 2014

@Barryoffshore
I think the problem is that Excel won't know what form has data in it and what form doesn't, therefore won't be able to print the required forms.
you state

For instance, if on a single sheet, there is data in cells E7, E37, E66 & E95, only the pages that have those cells in them will print.

My suggestion would be to list which cells are the "trigger" cells for ALL 21 pages, then tell us the range for each page.
Maybe then we could help !
I'd also suggest selecting each "page" and giving it a named range....and make the names concurrent like, Barry1, Barry2, etc.
That way we could loop through each "page" to see if it needed printing.

Your request is not quite as simple as it may appear to you...

Barryoffshore · Jul 21, 2014

I didn't think it was going to be easy... I am far from being anything more than a novice at best, but I try to read and learn before I ask on here. Having someone do something for me makes it hard for me to learn... but sometimes (lots of times!!!) I don't have any choice but to ask for help.

And, I don't know how to express the gratitude and admiration I have for all of who have helped me - today and every time in the past!

Bear

Barryoffshore · Jul 21, 2014

It will take me a little while to gather the "trigger" cells and ranges - working on that now... QUESTION! Can the Named Ranges be the same for each of the 87 sheets? I honestly don't know much about "Named Ranges" but I do know a little and will do my best.

THANK YOU all for being so patient. I must seem like a complete idiot compared to the level of brilliance you all have!!!

Michael M said:
@Barryoffshore
I think the problem is that Excel won't know what form has data in it and what form doesn't, therefore won't be able to print the required forms.
you state

My suggestion would be to list which cells are the "trigger" cells for ALL 21 pages, then tell us the range for each page.
Maybe then we could help !
I'd also suggest selecting each "page" and giving it a named range....and make the names concurrent like, Barry1, Barry2, etc.
That way we could loop through each "page" to see if it needed printing.

Your request is not quite as simple as it may appear to you...

Michael M · Jul 21, 2014

Hmm, forgot about the 87 sheets...

....No, the named ranges have to be different !
I'm guessing the trigger cells will be the same on each sheet ??
If so, maybe a list of the trigger cells and the range to be printed relative to each trigger.
Are the forms on each worksheet, the same for ALL 87 sheets, just with different data ??

Barryoffshore · Jul 21, 2014

Yes, every sheet is exactly the same, just different sheet (tab) names. I named them all and have the "trigger" cells - let me get the ranges for each page to be printed and I'll get back with you.

Barryoffshore · Jul 21, 2014

Here are the trigger cells and range for each page - and I really hope I did this right:

 TRIGGER CELL E 7 RANGE $A1:$AC30
TRIGGER CELL E 37 RANGE $A34:$AC60
TRIGGER CELL E 66 RANGE $A63:$AC89
TRIGGER CELL E 95 RANGE $A92:$AC118
TRIGGER CELL E 124 RANGE $A121:$AC147
TRIGGER CELL E 153 RANGE $A150:$AC176
TRIGGER CELL E 183 RANGE $A180:$AC206
TRIGGER CELL E 212 RANGE $A209:$AC235
TRIGGER CELL E 241 RANGE $A238:$AC264
TRIGGER CELL E 270 RANGE $A267:$AC293
TRIGGER CELL E 299 RANGE $A296:$AC322
TRIGGER CELL E 329 RANGE $A326:$AC352
TRIGGER CELL E 358 RANGE $A355:$AC381
TRIGGER CELL E 387 RANGE $A384:$AC410
TRIGGER CELL E 416 RANGE $A413:$AC439
TRIGGER CELL E 445 RANGE $A442:$AC468
TRIGGER CELL E 475 RANGE $A472:$AC498
TRIGGER CELL E 504 RANGE $A501:$AC527
TRIGGER CELL E 533 RANGE $A530:$AC556
TRIGGER CELL E 562 RANGE $A559:$AC585
TRIGGER CELL E 591 RANGE $A588:$AC614


I can't thank you enough for helping me!!!

Bear

Michael M · Jul 21, 2014

Hey Bear
This assumes that the trigger cell is not blank, to force the PrintOut
I notice some line are 29 rows apart and some are 30 !
Is that correct ?? or is there an error?
Also, if it's correct is it possible to make each "gap" the same ?, ie ALL 29 or ALL 30.
Otherwise, try this....but I'd suggest
1. try it on a test workbook
2. Use F8 to step through manually, rather than just hitting run. That way you won't print heaps of paper for nothing !!!

Code:

Sub MM1()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
    ws.Activate
    If Range("E7").Value <> "" Then Range("A1:AC30").PrintOut
    If Range("E37").Value <> "" Then Range("A34:AC60").PrintOut
    If Range("E66").Value <> "" Then Range("A63:AC89").PrintOut
    If Range("E95").Value <> "" Then Range("A92:AC118").PrintOut
    If Range("E124").Value <> "" Then Range("A121:AC147").PrintOut
    If Range("E153").Value <> "" Then Range("A150:AC176").PrintOut
    If Range("E183").Value <> "" Then Range("A180:AC206").PrintOut
    If Range("E212").Value <> "" Then Range("A209:AC235").PrintOut
    If Range("E241").Value <> "" Then Range("A238:AC264").PrintOut
    If Range("E270").Value <> "" Then Range("A267:AC293").PrintOut
    If Range("E299").Value <> "" Then Range("A296:AC322").PrintOut
    If Range("E329").Value <> "" Then Range("A326:AC352").PrintOut
    If Range("E358").Value <> "" Then Range("A355:AC381").PrintOut
    If Range("E387").Value <> "" Then Range("A384:AC410").PrintOut
    If Range("E416").Value <> "" Then Range("A413:AC439").PrintOut
    If Range("E445").Value <> "" Then Range("A442:AC468").PrintOut
    If Range("E475").Value <> "" Then Range("A472:AC498").PrintOut
    If Range("E504").Value <> "" Then Range("A501:AC527").PrintOut
    If Range("E533").Value <> "" Then Range("A530:AC556").PrintOut
    If Range("E562").Value <> "" Then Range("A559:AC585").PrintOut
    If Range("E591").Value <> "" Then Range("A588:AC614").PrintOut
Next ws
Application.ScreenUpdating = True
End Sub

Barryoffshore · Jul 22, 2014

This works and only prints the pages in which the trigger cells are true, however it prints out for all 87 sheets. I cleared all the trigger cells for all but 1 sheet and ran the macro - and it only printed the one sheet, but it printed 87 copies of that one sheet.

You were right - this is a tough one!

Thank you for all your help!!!

Barryoffshore · Jul 22, 2014

Hey Michael M. - I figured out how to fix it!!! Look at the code below and you'll see the little change I made to the code you wrote - now it prints the pages with data, but only in the sheet I'm in... THANK YOU SO VERY MUCH FOR ALL YOUR HELP!!!

Code:

Sub BLM_PRINTSHEETS_THISWORKBOOK()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWindow.SelectedSheets
    ws.Activate
    If Range("E7").Value <> "" Then Range("A1:AC30").PrintOut
    If Range("E37").Value <> "" Then Range("A34:AC60").PrintOut
    If Range("E66").Value <> "" Then Range("A63:AC89").PrintOut
    If Range("E95").Value <> "" Then Range("A92:AC118").PrintOut
    If Range("E124").Value <> "" Then Range("A121:AC147").PrintOut
    If Range("E153").Value <> "" Then Range("A150:AC176").PrintOut
    If Range("E183").Value <> "" Then Range("A180:AC206").PrintOut
    If Range("E212").Value <> "" Then Range("A209:AC235").PrintOut
    If Range("E241").Value <> "" Then Range("A238:AC264").PrintOut
    If Range("E270").Value <> "" Then Range("A267:AC293").PrintOut
    If Range("E299").Value <> "" Then Range("A296:AC322").PrintOut
    If Range("E329").Value <> "" Then Range("A326:AC352").PrintOut
    If Range("E358").Value <> "" Then Range("A355:AC381").PrintOut
    If Range("E387").Value <> "" Then Range("A384:AC410").PrintOut
    If Range("E416").Value <> "" Then Range("A413:AC439").PrintOut
    If Range("E445").Value <> "" Then Range("A442:AC468").PrintOut
    If Range("E475").Value <> "" Then Range("A472:AC498").PrintOut
    If Range("E504").Value <> "" Then Range("A501:AC527").PrintOut
    If Range("E533").Value <> "" Then Range("A530:AC556").PrintOut
    If Range("E562").Value <> "" Then Range("A559:AC585").PrintOut
    If Range("E591").Value <> "" Then Range("A588:AC614").PrintOut
        Next ws
Application.ScreenUpdating = True
End Sub

CharlesH · Jul 22, 2014

Hi,

I see you have a answer. But thought I'd show you my code.
My code is a little longer.

Code:

Sub Page_Print()
Dim p As Long
Dim ws As Worksheet
p = 1
For Each ws In ActiveWindow.SelectedSheets
    ws.Activate
    For x = 1 To Application.ExecuteExcel4Macro("Get.Document(50)")
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E7").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E37").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E66").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E95").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E124").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E153").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E183").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E212").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E241").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E270").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E299").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E329").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E358").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E387").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E416").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E445").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E475").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E504").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E533").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E562").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    ElseIf Range("E591").Value <> "" Then
        ActiveSheet.PrintOut From:=x, To:=x
    End If
    p = p + 1
    Next x
Next ws
End Sub

Print Macro Help - Is it possible to Macro so only the pages in a sheet that have data in certain cells are printed?

Barryoffshore

Board Regular

Michael M

Well-known Member

Excel Facts

Barryoffshore

Board Regular

Barryoffshore

Board Regular

Michael M

Well-known Member

Barryoffshore

Board Regular

Barryoffshore

Board Regular

Michael M

Well-known Member

Barryoffshore

Board Regular

Barryoffshore

Board Regular

CharlesH

Active Member

Similar threads

Forum statistics

Share this page

Print Macro Help - Is it possible to Macro so only the pages in a sheet that have data in certain cells are printed?

Board Regular

Well-known Member

Excel Facts

Board Regular

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Board Regular

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock