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]

I have tried the code previously suggested in this thread but it isn't working for some reason. (I think it is looping incorrectly.) I have recapped what I need in my post from 12/12 at 11:11 below. If someone can look at this and offer a suggestion, I would appreciate it.

If I can't get more suggestions, I will probably just enter the code to look at each sheet, do a comparison, print or not print, and then go on to the next sheet rather than having it done in a loop. I know it is not the preferred method, but it is only 31 pages.

Thank you for your help!

 

[Norie]

Gary

Are any of the sheets chart sheets or some other type of sheet?

 

[gnrboyd]

No chart sheets. They are just standard worksheets. I think at least part of the problem with the code is the first line. (for each sheet in workbook......) I am not looking at each sheet in the workbook. Only 31 out of 35 sheets. Then when I alter your code from 31 to a lower number for testing, the code still loops 35 times. I'm getting very odd results and difficult to track exactly what is going on.

 

[Norie]

Did you check the names?

 

[gnrboyd]

Yes, the sheets I want to evaluate before printing are numbered 1 through 31 for each day of the month.

 

[Norie]

And you're 100% sure none of them have leading/trailing spaces in the name?

 

[Datsmart]

The posted code worked in a test file for me.
Thought I had an error, but noticed I had a Text number and not a true number in cell B2 of one of my sheets.
Are all your values true numbers and not text?
"=ISNUMBER(B2)" should show TRUE.

 

[gnrboyd]

Yes, all of the values are numeric. (I used the isnumber and all show true) Sometimes I do put two values in that cell that are added together. (=100+50) Also, the cell B2 is actually merged with B3 and B36 is merged with B37. I thought that might be the problem but I did a test on a couple of pages by unmerging them but the problem still existed so I thought I ruled that out.

I am positive the names of the sheets to not have leading/trailing spaces.

Maybe I need to do more testing on the merged cell theory..??

 

[gnrboyd]

John,

For a test, can you change your page range from 1-31 to something less and see if the macro loops the correct number of times?

 

[Datsmart]

As written, it is going to loop as many times as there are worksheets, but that is not a problem because the "Case 1 To 31" line will only look at sheets named 1 thru 31.