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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
How about this.
Code:
Dim ws As Worksheet

   For Each ws In Worksheets
        With ws
             If .Range("B2").Value > 0 Or .Range("B36").Value > 0 Then
                  .Printout
             Endif
         End With
   Next ws
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
I guess I should have mentioned that besides the pages 1-31, I also have a couple of other worksheets in the template that would not need to be printed. (One of them is before the pages 1-31, and the other 3 are at the end.) How can this be modified so that only pages 1-31 go through this loop? Thank you.
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
How are the sheets named?
1
2
3
4
etc.
Or
Sheet1
Sheet2
Sheet3
or something else?

Michael
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563

ADVERTISEMENT

1, 2, 3,.....31
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Try this.
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:=True
                    End If
                Case Else
                ' do nothing
            End Select
        End With
    Next ws
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563

ADVERTISEMENT

I will try that later this afternoon. (Although coming from you I am positive it will work.) Thank you very very much!!
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
I happened to think of one other twist to my problem.

The printer these pages should go to may or may not be be the default printer for the user printing the pages. In the past, I have recorded simple print macros that change the active printer to a specific network printer, print the selected pages, and then change the active printer back to my default printer. (I do this so that the next time I need to print, I don't have to change my selected printer back to my default.) That works fine when I am the only user. How can I change the selected printer back to the generic default printer for a particular user? (Or is this even possible?)

Thank you
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Well you can get the name of the active printer like this.
Code:
strActPrt = Application.ActivePrinter
MsgBox strActPrt
So theoretically you could capture the user's active printer's name, then print with the required printer and then change the active printer back.
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
This is starting to get over my head quickly. I will take a stab at it later this afternoon. Thanks again for your assistance!
 

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