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.
 
This seems to work:
Code:
Sub Macro10()
Dim WS As Worksheet

    For Each WS In Worksheets
        With WS
            Select Case WS.Name
            Case 1 To 9
            If WS.Name > 0 And WS.Name < 32 Then
                If .Range("B2").Value > 0 Or .Range("B36").Value > 0 Then
                   '.PrintOut Preview:=False
                   MsgBox "WorkSheet Named " & WS.Name & " will Print." & vbCrLf _
                        & "B2  value = " & .Range("B2").Value & vbCrLf _
                        & "B36 value = " & .Range("B36").Value
                End If
            End If
            Case Else
                ' do nothing
            End Select
        End With
    Next
End Sub
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
John,

I think you may be correct with your assessment.

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.

However, I have adjusted the range from 1-31 to 4-5 and sheets 4 and 5 did print. I have tried other variations of sheet ranges though, and still had problems with sheets 4-9. The range testing was inconsistant.

In a previous post you asked about the 2 digit cells...When I referenced 2 digits, I was meaning the 2 digit tab numbers. Also, to answer another previous post...the numbers that are in cells B2 and B36 on the non-printing sheets are just numbers ranging from around 10,000 to 100,000. I think you already by passed these questions with your last 2 posts but I thought I would answer them anyway.

I will try your revised code when I get a chance. However, I don't quite understand what it is trying to do. I am still baffled as to how your testing of the initial code worked but mine doesn't. (I am using Excel 2000) If your initial testing worked then this code still might not work for me. If it doesn't, I think I will just use 31 separate commands that look at each sheet to determine if it shoudl print or not. I actually tried that earlier today but it didn't work. I'm a record and tweak macro user and do not understand visual basic. If you could help me with what that line would look like I would appreciate it. Then I will just copy/paste it and edit the sheet number. I know it isn't the most efficient but since the loop will never be larger than 31, it shouldn't make much difference.

I really appreciate your assistance with this!!

Thank you.

Gary
 
Upvote 0
The only thing I can think of is if we convert the name of the workbook to a number using Val.
 
Upvote 0
John/Norie,

I tried out John's revised code and it now seems to be working fine. Can you by chance explain what the code is doing and perhaps why this modification seems to fix the problem?

Thanks to both of you!
 
Upvote 0
gnrboyd
Using Case 1 to 9 allows the code to look at sheet names that have numbers that start with 4, 5, - 9. The Case 1 to 31 only included sheets that started with 1, 2, or 3.
The line;
Code:
If WS.Name > 0 And WS.Name < 32 Then
allows only sheets with Names that are between 1 and 31 to be considered in that part of the code.
Glad it is working for you now.
 
Upvote 0
If I am looking at each page anyway, do I still need the Case 1-9? Won't the
If WS.Name > 0 And WS.Name < 32 Then
part of the code take care of sheets 1-9 also?

Someday I keep thinking I am some of this code is going to soak in....then again....I keep saying I'm going to win the lottery also....
 
Upvote 0
Yes you can, using Norie's suggestion to use Val()
Code:
Sub Macro10()
Dim WS As Worksheet
    For Each WS In Worksheets
            If Val(WS.Name) > 0 And Val(WS.Name) < 32 Then
                If WS.Range("B2").Value > 0 Or WS.Range("B36").Value > 0 Then
                   '.PrintOut Preview:=False
                   MsgBox "WorkSheet Named " & WS.Name & " will Print." & vbCrLf _
                        & "B2  value = " & WS.Range("B2").Value & vbCrLf _
                        & "B36 value = " & WS.Range("B36").Value
                End If
            End If
    Next
End Sub
 
Upvote 0
John/Norie,

I finally got around to trying your latest code. When I removed the quote from

'.PrintOut Preview:=False

to make the sheets print, I got a compile error stating invalid or unqualified reference.

For now, I have used John's previous code and it is working fine. I would like to use the code without the 2 separate ranges (1-9 and 1-31) as it will be easier to follow if I ever need to review or reuse this code down the road. I'm sure I would look at it and not remember why it was done this way since it seems to be some type of limitation within Excel.

Anyway, if you could give me a clue as to why I am getting the error I would greatly appreciate it.

Thanks
 
Upvote 0
I think I just figured it out. If I add WS to the code it seems to work. (WS.PrintOut Preview:=False)

Thanks again for all of your help!
 
Upvote 0
Sorry, I did not check the PrintOut code behind the single quote.
Needed to assign a sheet reference.
Code:
Sub Macro10()
Dim WS As Worksheet
    For Each WS In Worksheets
            If Val(WS.Name) > 0 And Val(WS.Name) < 32 Then
                If WS.Range("B2").Value > 0 Or WS.Range("B36").Value > 0 Then
                   WS.PrintOut Preview:=False
                   'MsgBox "WorkSheet Named " & WS.Name & " will Print." & vbCrLf _
                        & "B2  value = " & WS.Range("B2").Value & vbCrLf _
                        & "B36 value = " & WS.Range("B36").Value
                End If
            End If
    Next
End Sub
I commented out the msgbox section so it will just print your pages now with no message other than what your Print command shows.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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
Back
Top