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

Written by Allen Wyatt (last updated December 26, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003


1

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.

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 (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

Jumping to Styles in the Task Pane

Mouse versus keyboard selection of styles in Word.

Discover More

Adding Leading Zeroes to ZIP Codes

Import a bunch of ZIP Codes into Excel, and you may be surprised that any leading zeroes disappear. Here's a handy little ...

Discover More

A Shortcut for Switching Focus

While not technically an Excel-only tip, the shortcuts described in this tip will help you switch focus from your ...

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)

Printing Limited Pages from a Range of Worksheets

Need to print just a few pages from a group of worksheets? The easiest way to handle the task may be through a macro, as ...

Discover More

Showing Filter Criteria on a Printout

When you print out a filtered worksheet, you may want some sort of printed record as to what filtering was applied to the ...

Discover More

Setting Up Your Printer

Need your printed output to look its best? You may need to change the settings used by your printer, then. Here's how to ...

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}] (all 7 characters, in the sequence shown) 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?

2021-05-04 10:27:24

Hannah

I have a giant spreadsheet that I use for tracking businesses that our salesman stops at each month. It's a pretty big sheet, with roughly 1800 rows. Is there a way for me to only print the rows that have information in say, column C? I would like to be able to print the notes for our salesman without printing the entire workbook.


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.