View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
B. Costa B. Costa is offline
external usenet poster
 
Posts: 2
Default Print Selected Items in Excel Order Form

Hi,
Im new to the site and also to Visual Basic. I have found the following
code from Dave Peterson on http://www.contextures.com/xlForm03.html, which
does more or less what I want apart from one thing, which I cant figure out
how to change. Values from the worksheet list, starting in Column B and
moving right, are copied to the order form, into the addresses specified.
Instead of copying values from Column B and moving right (as in B1, C1,
D1...), I would like to copy to the form only selected cells like B1, D1,
H1...

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