Written by Allen Wyatt (last updated April 8, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13108) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Conditional Printing.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you have a workbook containing many worksheets, you might want to print only those worksheets that have some sort of ...
Discover MoreShould you print in portrait or in landscape? The decision can greatly affect the way your printout looks. Wouldn't it be ...
Discover MoreWhen you print a worksheet, Excel normally prints all the pages or a consecutive series of pages that you specify. If you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments