Search Contextures Sites
Print Selected Items in Excel Order Form
Thanks to Dave Peterson, who wrote this technique.
Add an "X" to items in a worksheet list, and the following code will print each marked item in an Excel order form.
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:
myAddresses = 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 = Worksheets("Order Form")
Set DataWks = Worksheets("Orders")
The "X" marks are cleared at the end of the code, to prepare for the next print session, and a message is displayed, indicating how many orders were printed.
After testing, you can change Preview to False in this line:
and the forms will print, instead of showing a preview.
Note: When you copy the code to your workbook, 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: http://www.mvps.org/dmcritchie/excel/getstarted.htm
The zipped sample Excel order forms workbook can be downloaded here:
Print Marked Items Code
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 myAddresses As Variant Dim lOrders As Long Set FormWks = Worksheets("Order Form") Set DataWks = Worksheets("Orders") myAddresses = 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 is not marked, do nothing Else .Offset(0, -1).ClearContents 'clear mark for the next time For iCtr = LBound(myAddresses) To UBound(myAddresses) FormWks.Range(myAddresses(iCtr)).Value _ = myCell.Offset(0, iCtr).Value Next iCtr Application.Calculate 'just in case 'after testing, change to 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
Contextures Inc., Copyright ©2012
All rights reserved.
Last updated: December 6, 2010 11:35 PM