print worksheets based upon criteria

[gnrboyd]

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.

 

[gnrboyd]

Thanks Norie!.....Your code worked like a charm.

 

[gnrboyd]

I finally got a chance to use the template discussed below but it isn't working as it should. When I first tested it, I thought it was working so I saved as a template and I am just now actually using it. The code used was:

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
End If
Case Else
' do nothing
End Select
End With
Next ws


It seems to work on most sheets but not all. One thought I had was that it has something to do with the fact that cells B2&B3 and B36&B37 are merged on each sheet. I have had some odd and inconsistant things happen formulas in merged cells before. Anyway, the list below shows the pages that should have printed and the ones that actually printed. (Sheets 6 through 9 should have printed but didn't.) Any ideas would be appreciated. Thanks!

SHEET# SHOULD PRINT ACTUALLY PRINTED
1 X X
2 X X
3 X X
4
5
6 X
7 X
8 X
9 X
10 X X
11
12
13 X X
14 X X
15 X X
16 X X
17 X X
18
19
20 X X
21 X X
22 X X
23
24 X X
25
26
27 X X
28 X X
29 X X
30 X X
31

 

[Norie]

Merged cells?

Are they really necessary?

As you seem to have found out they just cause problems.

Couldn't you use the Center across selection alignment?

 

[gnrboyd]

No, I suppose merged cells are not really necessary in the sheet. It just makes it look more appealing and eliminates possible entry errors.

Do you really think this could be the cause of the probelm? Since you didn't mention the code in your last post, I assume there are not issues with it.

If you think the issue is the merged cells, I will change the sheet and see if that solves the problem.

Thanks for your help!

 

[gnrboyd]

It looks like the merged cells isn't the problem. I unmerged the cells on a couple of the pages that should print and they still are not printing in the range from tabs 6-9. Any other suggestions? Is there something wrong in the looping of the code?

 

[gnrboyd]

Still a problem with printing pages based upon criteria.

I have played around with the code a bit and I think there is a problem with it. To recap my needs....

I have a workbook with tabs labeled 1 through 31 for days of the month. There are a few other pages also in the workbook. I need the macro to print all pages that have values in either of 2 cells (B2 and B36) that are not equal to zero. The code only needs to look at the sheets labeled 1-31 and not the other sheets. I played around with the code below and changed the case range from 3-6 for testing and the proper pages printed. When you change it back to 1-31, the correct pages do not print. Somehow I think there is a loop problem with the code.

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
End If
Case Else
' do nothing
End Select
End With
Next ws

I am really not familiar with VB code so all I am doing is speculating. I would sure appreciate any assistance.

Thank you

 

[Norie]

Why do you think there is a problem with the loop?

What problem, if any, do you think it is?

Have you checked the names of the sheets?

 

[gnrboyd]

Norie,

The names of the sheets are all correct. (Numbered 1 through 31 with no extra spaces on the tabs.) When I ran the macro, it missed pages that should have printed. I then changed the code to sheets 3-6 instead of 1-31 and I walked through the macro it looped more than 4 times. In fact it was closer to 30 or so. (I didn't count the exact number) This lead me to believe the code might be looping though each worksheet regardless of what the 1-31 or 3-6. When I ran it with the 3-6 code, the correct pages printed at least for that range.

I really don't know much about the VB code as I am a record and tweak macro user. I am stumped as to what to try next. I appreciate your help!

 

[Norie]

Do you want to send me the file?

If so then PM me and I'll send you my email address.

I'm a bit stumped myself because as far as I can see there shouldn't be any problem with the loop.

 

[gnrboyd]

Norie,

I played around with the code a bit more and here is what I came up with.
In my test, sheets 1, 4,5,6,7,8, & 11 should have printed based upon the values in cells B2 and B36. When I ran the macro pages 1 and 11 was all that printed.

I then changed the loop from case 1-31 to case 4-7 and pages 4,5,6,7 did print which is correct but how it got there may not be correct...read on.

I then changed the loop to case 4-10 and no pages printed.

In each of the 3 macro runs, code went through the loop 35 times which is not what it should do. It should only loop for the 31, 4, and 7 times respectively based upon the case range.

Since I don't know exactly what the values in the case # are doing in your code, I don't know what is going on and how to fix it. In my own non-VB brain, here is what I think the code should be doing.

For x = 1 to 31
x = sheet name
Select sheet name
If cells B2 or B36 <> 0, then print sheet , else do nothing
Next x

Is this what you think you code should be doing or did I not explain it correctly?

Thanks for your help.