Printing multiple worksheets with VBA based on Condition

WSBirch

Board Regular
Joined
Apr 10, 2018
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
Fantastic, thank you!
What would the code be for printing based on the conditional format to then run those two lines?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution
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.
 
Upvote 0
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
 
Upvote 0
Not to worry! Thank you! I was able to get this to work with your guidance!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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