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

Conditional Printing

by Allen Wyatt
(last updated December 19, 2017)

9

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. (Printing is one of the events that Excel allows you to trap.) The following macro, added to the thisWorkbook object, is run every time you try to print.

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13108) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel 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

Limits on Path Length in Word

When you organize your hard drive, it is easy to go hog-wild with folders and subfolders. You need to know that how you ...

Discover More

Controlling the Format of Cross-References

When you use fields to add cross-references to tables or figures, Word normally takes care of formatting the words used ...

Discover More

Getting Rid of Background Color in All Tables

When working with tables (particularly those created by others), you can spend a large amount of time getting the ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Printing Selected Cells by Default

Want a one-button approach to printing? Excel provides the Quick Print tool, but it may not do exactly what you want. ...

Discover More

Printing Rows Conditionally

Need to only print out certain rows from your data? It's easy to do if you apply the filtering or sorting techniques ...

Discover More

Printing All or Nothing

Want to make sure that when you worksheet is printed that everything in the workbook is really printed? You can ...

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 five more than 8?

2017-12-20 00:34:38

Kevin

I'd say YES the requested special printing requests are possible. I've created many similar myself.
You need to apply formulas on the worksheet for the various components you want, then introduce them into the macro in the appropriate options via the various print commands.
Except for basic requests you are unlikely to get answers here.
Teach yourself on macros or join a course like Allen provides.


2017-12-19 14:46:42

Gary Lundblad

Nice tip and macro! For a long time I've wanted a macro that would change the page set-up based on the number of visible rows in the print area. I print quite a few pages by selecting all tabs in a series, and then choosing print. I hate it when only one or two rows show up on a second page, but if I tell them to fit to one page, some with a lot of data will wind up with very small print. How hard would it be for a macro to cell, say A1, and then if that value is 1 set the worksheet to print one page wide by one page high; if it's a 2, then two columns wide by 2 pages high, etc...

Thank you!

Gary


2015-04-28 12:57:16

Janet

I'm sure this is easier than I think, I've just been away from Excel too long I think.

Say I'm working in a spreadsheet and I highlight something yellow. How can I get excel to only print if it's yellow? Ie I don't want too the other info on the printed page.


2014-10-22 18:11:51

John

The first formula helped me a lot.
Now I just need that formula but instead of printing, it will save conditional pages to a single PDF file.
Is it possible?


2014-09-11 20:59:36

Becky

I need a version of this code that selects which pages within a worksheet to print, instead of which worksheet in a workbook. Example:

If page 1, A1 = yes, print. If no, don't print.

Is this possible? Or can you only control which worksheets to print?

thanks for any help you can offer!


2014-06-02 11:55:05

Dave T

Sounds like Dee & Jim need a macro that runs when printing that first removes all existing page breaks, scans the specified column for a change in value, and inserts a page break if found. The hard (impossible?) part of this would be to know if the current page is odd or even so that 1 or 2 breaks could be inserted. Any way to do that?

Another trick I've used for printing is to offset all graphics objects with a macro that runs when printing. What's displayed on screen and on a printed page never quite line up the same, been a problem with Excel for a long, long time!


2014-05-20 21:42:37

Dee

Awesome! Love it! Works perfectly in my 22 sheet workbook. Thank you SO much!


2014-05-19 11:50:39

Duncan

Well, Jim S, that's a variation on the Holy Grail of Excel printing which many people have been seeking for years. Namely what you'd think of as a relatively simple reporting requirement to start a new page when some total level or other change is reached.

After all, this was a standard feature in the language I was using 30+ years ago on a computer that ran with a massive 96 Mb (yes really) of memory. But it's not something Excel, which is after all a spreadsheet that assumes close user interaction in its formatting, has ever to my knowledge provided.

I would love to be told differently.


2014-05-17 09:59:04

jim sheehan

I need to control printing when I am do front and back printing.
For example:

When a value changes in a column I want a new page to start. Also the new page should start on an odd page number. In other words if the new page is an even page number. print a blank page first then print the next row on an odd page.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.