Home > Macros > Basics > Printing Print Selected Items in Excel List Mark items in a worksheet list, and this macro will print each marked item in an Excel order form. There is also a sample file for printing orders with multiple items. Thanks to Dave Peterson, who wrote this technique.

Introduction

On a worksheet, create a list or orders, and leave the first column blank. When you are ready to print specific orders, put an X in column A, for those orders.

Each order that is marked will be printed, using the template on the Order Form sheet.

Customize the Code

Values from the worksheet list, starting in Column B and moving right, are copied to the order form, into the addresses you've specified in this line:

myAddr = Array("E5", "E6", "B10", _
  "E25", "B16", "C16", "D16")

Change those references to match your order form.

Near the start of the code, change the worksheet names to match your worksheet names:

Set FormWks = Sheets("Order Form")
Set DataWks = Sheets("Orders") 

Note: When you copy the code to your workbook, be sure to include the following line at the top of the code module:

Option Base 0 

If you're new to macros, you may want to read David McRitchie's intro at: https://www.mvps.org/dmcritchie/excel/getstarted.htm

Run the Macro

After you copy the macro code to your workbook, use the Macro command on the Excel Ribbon's View tab, to run the macro. At first, the macro is set to show a Preview of each order form, and you can change that to a Print command, after testing. The instructions are below.

The "X" marks are cleared at the end of the macro, to prepare for the next print session. Also, a message is displayed, to tell you how many orders were printed.

Print or Preview

After testing, you can change Preview to False in this line:

FormWks.PrintOut Preview:=True

and the forms will print, instead of showing a preview.

Print Marked Items Code

You can copy this code to a regular code module in your workbook, and run the macro when you want to print the marked items.

Option Base 0
Sub PrintUsingDatabase()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddr As Variant
Dim lOrders As Long

Set FormWks = Sheets("Order Form")
Set DataWks = Sheets("Orders")

myAddr = Array("E5", "E6", "B10", _
  "E25", "B16", "C16", "D16")

With DataWks
  'first row of data to
  '  last row of data in column B
  Set myRng = .Range("B3", _
    .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
  With myCell
    If IsEmpty(.Offset(0, -1)) Then
      'if the row not marked, do nothing
    Else
      'clear mark for the next time
      .Offset(0, -1).ClearContents
      For iCtr = LBound(myAddr) _
        To UBound(myAddr)
        FormWks.Range(myAddr(iCtr)).Value _
          = myCell.Offset(0, iCtr).Value
      Next iCtr
      Application.Calculate 'just in case
       'after testing, change Preview
       '  to False to Print
      FormWks.PrintOut Preview:=True
      lOrders = lOrders + 1
    End If
  End With
Next myCell

MsgBox lOrders & " orders were printed."

End Sub

Download the Sample File

Single Item Orders: Download the Excel order forms workbook described in the tutorial above. Each order has one item, and a set tax rate and shows the total amount. The zipped workbook is in xlsm format, and contains macros.

Multi Item Orders: Download an Excel order forms workbook with multiple items in each order. Enter the order info, and up to 10 items for each order. The zipped workbook is in xlsm format, and contains macros.

More Tutorials

Survey Form with Option Buttons

Excel Data Entry Worksheet Form

Excel Data Entry and Update Form