print worksheets based upon criteria

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
I have a monthly template that has 31 tabs labeled 1 through 31 for the days of the month. I would like to add a macro that would print only the sheets that have a value greater than 0 in either of two specific cells.

Example:

Sheet / Cell B2 / Cell B36 / Action
1 / 0.00 / 0.00 / Don't print
2 / 100.00 / 0.00 / Print
3 / 0.00 / 100.00 / Print
4 / 100.00 / 100.00 / Print

I could add a summary sheet that would pull over these values and/or add if statements that return results like "don't print" or "print" if it would be easier in the macro or I could just use the code in macro to determine to print or not.

I am a record and tweak macro user and this isn't something you can record. I found some simple code in another post that would print one page based upon single criteria but it isn't exactly what I was looking for.

I would appreciate any assistance. Thank you.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
I am wondering if the code is running faster than your print cache can catch the instructions sent to it. Try changing your code like this:
Code:
Sub Macro10()
Dim ws As Worksheet

    For Each ws In Worksheets
        With ws
            Select Case ws.Name
            Case 1 To 31
                If .Range("B2").Value > 0 Or .Range("B36").Value > 0 Then
                   .PrintOut Preview:=False
                   MsgBox "Sheet " & ws.Name & " has been sent to the Printer..."
                End If
            Case Else
                ' do nothing
            End Select
        End With
    Next ws
End Sub
The messagebox will allow enough time to let the sheet be put in the printer cache if you wait a few seconds before clicking "OK".
If all your pages get printed, then maybe you can put in some code to just pause for a few seconds like this:
Code:
Application.Wait (Now + TimeValue("00:00:03"))
This will pause for 3 seconds before continuing.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
In the current book I am printing, pages 1,4,5,6,7,8,11,12,13 should print.
When I print, I get 1,11,12,13. (4,5,6,7,8 do not print)

I un-merged cells B2/C2 and B36/C36 in sheets 4 and pages 4,5,6,7,8 still did not print. This means merged cells are not the problem.

I then changed the code to only look for sheets 4 and 5 and both sheets 4 and 5 did print. This means the sheet names are not the problem.

I'm still stumped. :eek:
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
I checked merged cells and it does not make a difference as long as the first cell of the merge is B2 or B36.
Try the suggestion in my previous post. I bet too much gets thrown at the printer cache and only several get picked up.
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
John,

I tried your suggestion and the sheets 4,5,6,7,8 still did not print. Good thought though.

Can some code be added to the "case else" like to report information on cells B2 and B36 to a message box before going to the next sheet. This might narrow things down. ??
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

Code:
Sub Macro10()
Dim ws As Worksheet

    For Each ws In Worksheets
        With ws
            Select Case ws.Name
            Case 1 To 31
            MsgBox .Range("B2").Value & " - " & .Range("B36").Value
                If .Range("B2").Value > 0 Or .Range("B36").Value > 0 Then
                   '.PrintOut Preview:=False
                   MsgBox "Sheet " & ws.Name & " will Pring."
                End If
            Case Else
                ' do nothing
            End Select
        End With
    Next ws
End Sub
I commented out the .PrintOut code for testing purposes.
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
John,

Thanks for the testing code. That should save some paper.

The pages 4 through 8 are not getting the message box at all. Even the other blank pages, still have the message box that pops up with - in it. I walked through the code and pages 4 - 8 go from the "case 1-31" code to "case else". When it hits sheet 10, the dashes start again. (I filled in something on sheets 9 and 10 even though they originally were blank so I could see where it begins working again.) The problems seems to be from sheet 4 to 9. It doesn't even seem to recognize the sheets enough to put them through the test.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

As you are steping through the code, hold your cursor over "ws.name", it will display what the variable is at that cycle of the code. May help in troubleshooting. Don't know what else to check. Sounds like you are going about checking in the right way.
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
John,

I ran last month's data through the same macro and the problem is again with pages 4-9. Could it have something to do with when the macro gets to a page with 2 digits, it corrects whatever problem is occuring? As I said before, when I change the range to a shorter range of pages to look at, the problem pages sometimes will print but not always. There doesn't seem to be any common denominator that I can find. I have to stop working on this for today but I will be back for more pain another day. Hopefully you can see something I can't.

If nothing else, I will just put 31 individual lines in if to look at each workshet. (That is if I can figure out how to write it.)

Thank you for all of your help so far. (Your's too Norie)
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
What do you mean by "Cells with two digits"?
A double digit number???
What exactly is in cells B2 and B36 in the sheets that will not print?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Looks like the problem is in the Case 1 to 31 part of the code.
Case 1 to 31 will only consider sheets named 1 thru 31 but no sheets names that start with 4 or above. Does that make sense?
Will look at a work-around.
 

Forum statistics

Threads
1,136,322
Messages
5,675,085
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top