Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Conditional Printing.

Conditional Printing

by Allen Wyatt
(last updated March 11, 2016)

11

Kirk asked if there is a way to conditionally control what is printed in Excel. For instance, cell A1 contains a value, and the value controls exactly what is printed. Perhaps if A1 contains 1, then Sheet1 is printed; if it contains 2, then Sheet1 and Sheet2 are printed.

The only way to do this is with a macro, and there are several approaches you can use. Consider the following very simple macro, which simply uses a Select Case structure to control the printing.

Sub PrintStuff()
    Dim vShts As Variant

    vShts = Sheets(1).Range("A1")
    If Not IsNumeric(vShts) Then
        Exit Sub
    Else
        Select Case vShts
            Case 1
                Sheets("Sheet1").PrintOut
            Case 2
                Sheets("Sheet2").PrintOut
            Case 3
                Sheets("Sheet1").PrintOut
                Sheets("Sheet2").PrintOut
        End Select
    End If
End Sub

Run this macro with the value 1, 2, or 3 in cell A1 of the first sheet, and the macro prints different things based on the value. If the value is 1, then Sheet1 is printed; if it is 2, then Sheet2 is printed; and if it is 3, then both Sheet1 and Sheet2 are printed. If you want different values to print different things, just modify the Select Case structure to reflect the possible values and what should be printed for each value.

A more comprehensive approach can be created, as well. Consider adding a "control sheet" to your workbook. This sheet would have the name of each worksheet in the workbook listed in the first column. If you put a value to the right of a worksheet name, in the second column, then a macro will print the corresponding worksheet.

The following macro can be used to create the "control sheet."

Sub CreateControlSheet()
    Dim i as integer

    On Error Resume Next   'Delete this sheet if it already exists
    Sheets("Control Sheet").Delete
    On Error GoTo 0

    Sheets.Add   'Add the WhatToPrint Sheet
    ActiveSheet.Name = "Control Sheet"

    Range("A1").Select   'Label the columns
    ActiveCell.FormulaR1C1 = "Sheet Name"

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Print?"

    Cells.Select
    Selection.Columns.AutoFit

    For i = 1 To ActiveWorkbook.Sheets.Count
        Cells(i + 1, 1).Value = Sheets(i).Name
    Next
End Sub

The macro first deletes any old control sheet, if it exists. It then adds a new worksheet named Control Sheet, and puts headers labels in columns A and B. It then lists all the worksheets in the workbook in column A.

With the control sheet created, you can then place an "X" or some other value (such as "Y" or 1) into column B beside each worksheet you want to print. The following macro then examines the control sheet and prints any worksheet that has a mark—any mark—in the cell in column B.

Sub PrintSelectedSheets()
    Dim i as Integer
    i = 2

    Do Until Sheets("Control Sheet").Cells(i, 1).Value = ""
        If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
            Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select
            ActiveWindow.SelectedSheets.PrintOut Copies:=1
        End If
        i = i + 1
    Loop
End Sub

Another approach is to create a macro that runs just before printing. (This is one of the events—printing—that Excel allows you to trap.) The following macro, added to the thisWorkbook object, is run every time you try to print or choose Print Preview.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim vShts As Variant
    Dim iResponse As Integer
    Dim bPreview As Boolean

    On Error GoTo ErrHandler

    vShts = Sheets(1).Range("A1")
    If Not IsNumeric(vShts) Then
        GoTo InValidEntry
    ElseIf vShts < 1 Or vShts > Sheets.Count Then
        GoTo InValidEntry
    Else
        iResponse = MsgBox(prompt:="Do you want Print Preview?", _
          Buttons:=vbYesNoCancel, Title:="Preview?")
        Select Case iResponse
            Case vbYes
                bPreview = True
            Case vbNo
                bPreview = False
            Case Else
               Msgbox "Canceled at User request"
               GoTo ExitHandler
        End Select

        Application.EnableEvents = False
        Sheets(vShts).PrintOut Preview:=bPreview
    End If

ExitHandler:
    Application.EnableEvents = True
    Cancel = True
    Exit Sub

InValidEntry:
    MsgBox "'" & Sheets(1).Name & "'!A1" _
        & vbCrLf & "must have a number between " _
        & "1 and " & Sheets.Count & vbCrLf
    GoTo ExitHandler

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

The macro checks the value in cell A1 of the first worksheet. It uses this value to determine which worksheets should be printed. In other words, a 1 prints the first worksheet, a 2 prints the second, a 3 prints the third, and so on.

If the value in A1 is not a value or if it is less than 1 or greater than the number of worksheets in the workbook, then the user is informed that the value is incorrect and the macro is exited.

Assuming the value in A1 is within range, the macro asks if you want to using Print Preview. Depending on the user's response, the macro prints the specified worksheet or displays Print Preview for that worksheet.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2372) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Conditional Printing.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Absolutely Positioning a Graphic

Want a graphic to appear at a precise place on the page? It's easy to gain control by following the steps in this tip.

Discover More

Using Only Odd Page Numbers

Do you need to number the pages of your document using only odd page numbers? Word doesn't provide a way to do this, but you ...

Discover More

Setting Print Quality

When printing information in a workbook, you may want to take advantage of the different print quality settings available in ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Working With Multiple Printers

If you have multiple printers accessible to your computer, you may need a way to quickly print your worksheet on a specific ...

Discover More

Repeating Rows on a Printout Except On the Last Page

When setting up a worksheet for printing, you can specify that Excel repeat some of your rows at the top of each page ...

Discover More

Repeating Rows at the Bottom of a Page

Excel allows you to repeat rows at the top of every page of a printout. If you want to repeat rows at the bottom of every ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2017-03-25 07:18:23

Willy Vanhaelen

@Matt
Consider buying "Fine Print", a terrific program, which allows you to do what you want and a lot more.


2017-03-24 15:24:03

Matt

Hey,

I am wondering, using the first "Select Case" code for printing shown above, is there a way to do duplex, or double sided printing, if a 3 is entered as the value? I don't mind leaving the Excel print settings on Double Sided mode, but with the two separate ".printout" lines for each worksheet, Excel will send the two sheets to the printer separately and it will print on two sheets of paper, even if you have double sided mode selected. Is there a way to combine the two sheets (or print areas of those sheets) into one packet before it gets sent to the printer, so that the printer will recognise it should print on one paper? Thanks a lot,

Matt


2017-03-12 18:04:44

tony

I am creating a work book that when the data is collected it shows up on a hidden sheet and I have a vba print button that prints the hidden sheet I would like the vba button to print only visible data I have up to 125 lines and if the data only fills up 25 lines for that particular sheet then I want only 25 lines to be printed not the whole 125 lines of blank data can anyone help with a vba code for this????


2017-01-26 10:46:20

Persio Goris

I wanted to format a table to span for pages, but only wanted to print pages if Sheet1 A2, had a value Print Sheet2 page 1; If Sheet A9, had a value print sheet2 page 2; ....


2016-11-02 02:28:57

sridhar

i have 6 worksheet, 4th sheet was an invoice in which inoice no generated automatically while printing. After clubbing the new vba code whenever i print the other sheets the invoice no gets change.pls help me out.this is the code.

Private Sub CommandButton1_Click()
Dim sheetname As String
sheetname = Application.InputBox("Enter a SheetName")
Sheets(sheetname).PrintOut preview:=True
Dim num As Integer
Range("H1").Select
num = Range("H1").Value
num = num + 1
Range("H1").Value = num

End Sub


2016-10-26 17:51:27

omer shafi

Is there a way to specify to use a particular printer when printing my spreadsheet, but use the default printer for everything else?.... I have 2 printers, but when I print this particular spreadsheet, I want to use a specific printer


2016-10-26 17:21:52

omer shafi

I have a worksheet, and was wondering if you could guide me.

The workbook has 24 worksheet,

Main worksheet
label1
label2
label3 ..... & so on


I would like to enter a value in cell F4 on the main worksheet and it print the corresponding # of worksheets. If I enter 1, then only the first worksheet, if I enter 2, then worksheet 1&2, if I enter 3, then worksheet1,2,3

Thx for your guidnace


I was suing the following VBA per your website

Sub PrintStuff()
Dim vShts As Variant

vShts = Sheets(1).Range("F4")
If Not IsNumeric(vShts) Then
Exit Sub
Else
Select Case vShts
Case 1
Sheets("Label1").PrintOut
Case 2
Sheets("Label1").PrintOut
Sheets("Label2").PrintOut
Case 3
Sheets("Sheet1").PrintOut
Sheets("Sheet2").PrintOut
Sheets("Label3").PrintOut
Case 4
Sheets("Label1").PrintOut
Sheets("Label2").PrintOut
Sheets("Label3").PrintOut
Sheets("Label4").PrintOut
End Select
End If
End Sub


2016-08-17 13:23:18

Olivia

My boss wants me to set a print rule for an order form I created. He wants to print only the items ordered and exclude unordered or zero quantity from being printed. Is there a rule for that?


2015-08-27 07:54:25

Cam Cecil

Nice tip Allen.

Similar functionality can in fact be done without using macros.

In excel the print area is defined under Formulas -> Name Manager -> "Print_Area"

You can use regular excel functions to define the ranges, separating each conditional statement with comment.

For example, if I wanted to format a table to span for 2 pages, but only wanted to print page 2 if the first row of page 2 column B had a value:

=Sheet1!$A$1:$F$46,IF(Sheet1!$B$47<>"", Sheet1!$A$47:$F$95, "")


2015-04-03 01:55:39

Anurag Gupta

Hey,

We have a excel template with details like prepared by, reviewed by, approved by alongwith other few values.

We want to prevent few non-continuous range of cells from printing (but they should be visible for editing in worksheet).

Can some help with non-macro bases conditional printing methodology or help me with macro based conditional printing alongwith example excel workbook.

Thanks in advance
Anurag Gupta
Goa
India


2015-04-01 12:27:26

Ponsot

Hello,
Please, I try to prevent user from any printing of a certain Excel file.
I want them to fill in the Excel sheet instead of print+write on the piece of paper.

I tried with a password for example
Private Sub Workbook_beforePrint(Cancel As Boolean)

Secret = "noprint"
Question = InputBox("Mot de passe pour l'impression ?")

If Question <> Secret Then
MsgBox ("No printing nor scribbling on paper pages. Please enter your hours and narratives on this file sheet 2")
Cancel = True
End If
End Sub
But the print event is still running, not blocked.
Could you help me please, (and save the trees !)

Best regards
C.Ponsot


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.