Hi Dave
Never thought I would get the reply from the man himself, I have tried the
below and it works brilliantly, thanks for the quick reply and for writing
the code in the first place.

Kind Regards
Bruno


"Dave Peterson" wrote:

This retrieves the values from the cells based on the offset from column B.

myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16")
myOffsets = Array(0, 1, 3, 5, 9, 15, 33)

So column B (offset 0) goes to E5
column C (offset 1) goes to E6
column E (offset 3) goes to B10
...
Column AI (offset 33) goes to D16



Option Base 0
Option Explicit
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 myOffsets As Variant 'from column B
Dim lOrders As Long

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

myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16")
myOffsets = Array(0, 1, 3, 5, 9, 15, 33)

If UBound(myOffsets) < UBound(myAddresses) Then
MsgBox "Design error!"
Exit Sub
End If

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



B. Costa wrote:

Hi,
I€„¢m 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 can€„¢t 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

--

Dave Peterson
.