Printing multiple worksheets with VBA based on Condition

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Good morning,

I'm trying to figure out how to create a macro/button to print 2 worksheets, out of the several worksheets that exist in my workbook, but printing them under some conditions;
1. If cell S19=TRUE, and If cell P13=TRUE, then print 3 pages of each both worksheets.
2. If cell S19=TRUE, and If cell P13=FALSE, then print 2 pages of each both worksheets.

How might I go about doing this? I've seen a bit of printing code for VBA but none yet that have a conditional like this.
Help is greatly appreciated, thank you!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Ron de Bruin

Active Member
Joined
Aug 1, 2006
Messages
271
Platform
  1. Windows
  2. MacOS
You can test the cells you want and run this two code lines for 2 pages

Sheets("Sheet1").PrintOut From:=1, To:=2
Sheets("Sheet2").PrintOut From:=1, To:=2

Or for 3 pages

Sheets("Sheet1").PrintOut From:=1, To:=3
Sheets("Sheet2").PrintOut From:=1, To:=3
 

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Fantastic, thank you!
What would the code be for printing based on the conditional format to then run those two lines?
 

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Well, I've tinkered with conditionals a little bit and I have something going, but I don't think I want PrintOut From-To, so much as PrintOut Copies? I'm looking to print 2 copies of each worksheet or 3 copies of each worksheet, not pages 1 through 2 or 1 through 3, since each worksheet is only 1 page long.

However, my new code looks like this, but I'm having issues with the numbers of copies being printed.
Currently, Cell S19 = FALSE and Cell P13 = TRUE.
With this current code below, it's sending it to what I think is line 12 & 13, to print 2 copies of Sheet1. Correct me if I'm wrong, but shouldn't it be sending me to line 9 & 10, to print 3 copies of Sheet1? Right now, it's only printing 2 copies which makes me think it's skipping the "IF P13=True Then" and jumping down to the Else to print 2 copies instead?


I'm not great at cleaning this up, but basically;
If P13 is TRUE and S19 is FALSE, then print 3 copies of Sheet1.
If P13 is TRUE and S19 is TRUE, then print 3 copies of Sheet1 and Sheet2.

If P13 is FALSE and S19 is FASLE, then print 2 copies of Sheet1.
If P13 is FALSE and S19 is TRUE, then print 2 copies of Sheet1 and Sheet2.

VBA Code:
Sub Printing()
    If S19 = False Then
        GoTo SinglePage
    Else
        GoTo MultiPage
    End If
           
SinglePage:
    If P13 = True Then
        Sheets("Sheet1").PrintOut Copies:=3
        GoTo LastLine
    Else
        Sheets("Sheet1").PrintOut Copies:=2
        GoTo LastLine
    End If
           
MultiPage:
    If S19 = True And P13 = True Then
        Sheets("Sheet1").PrintOut Copies:=3
        Sheets("Sheet2").PrintOut Copies:=3
        GoTo LastLine
    Else
        Sheets("Sheet1").PrintOut Copies:=2
        Sheets("Sheet2").PrintOut Copies:=2
        GoTo LastLine
    End If
   
   
LastLine:
End Sub
 

Ron de Bruin

Active Member
Joined
Aug 1, 2006
Messages
271
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Try this test

The data to check is on a sheet named "Check"

and it print "Sheet1" and "Sheet2"

Sub test()
Dim CheckSheet As workksheet
Set CheckSheet = Sheets("Check")

If CheckSheet.Range("S19").Value = True And CheckSheet.Range("P13").Value = True Then
Sheets("Sheet1").PrintOut From:=1, To:=3
Sheets("Sheet2").PrintOut From:=1, To:=3
End If

If CheckSheet.Range("S19").Value = True And CheckSheet.Range("P13").Value = False Then
Sheets("Sheet1").PrintOut From:=1, To:=2
Sheets("Sheet2").PrintOut From:=1, To:=2
End If

End Sub
 
Solution

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Good morning,
Thanks for the update, it only printed 1 page each.
I think this is because it should be PrintOut Copies? Instead of From-To? Since each worksheet is only 1 page, I just need 3 or 2 copies of each.
 

Ron de Bruin

Active Member
Joined
Aug 1, 2006
Messages
271
Platform
  1. Windows
  2. MacOS
Good morning,
Thanks for the update, it only printed 1 page each.
I think this is because it should be PrintOut Copies? Instead of From-To? Since each worksheet is only 1 page, I just need 3 or 2 copies of each.
Correct, I not understand you correct
 

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Not to worry! Thank you! I was able to get this to work with your guidance!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,228
Members
416,963
Latest member
samfuge

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