PDA

View Full Version : Print selected rows to labels



aalphons
05-14-2008, 10:19 PM
The code below prints each selected row to a separate page.
How do I modify to print all selected rows continuously on one page?
Thanks!



Private Sub cmdPrintSelected_Click()
Dim DataWks As Worksheet
Dim RptWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant
Dim Records As Long
Set DataWks = Worksheets("Log")
Set RptWks = Worksheets("Report1")

myAddresses = Array("D1", "F2", "F3", "F1", "A1", "A2", "A3", "B3", "C3", "D2", _"D3", "A5", "F5", "A7")
With DataWks
Set myRng = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
Else
.Offset(0, -1).ClearContents
For iCtr = LBound(myAddresses) To UBound(myAddresses)
RptWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate
RptWks.PrintOut Preview:=True
Records = Records + 1
'RptWks.Range("A1:G50").ClearContents
End If
End With
Next myCell

MsgBox Records & " Record(s) Printed."
End Sub

Simon Lloyd
05-14-2008, 10:45 PM
Considering your title is to do with printing labels you haven't mentioned it in your question, however is suspect that if you set your printer paper type to labels (or ths size of your label) and then choose print and the amount of copies!

aalphons
05-15-2008, 08:30 AM
Considering your title is to do with printing labels you haven't mentioned it in your question, however is suspect that if you set your printer paper type to labels (or ths size of your label) and then choose print and the amount of copies!
Pardon my error and allow me to clarify. I don't actually need it to print labels.

It currently prints a page for each selected record.
I need it to copy subsequent records to the next available row in "Report1" so that they all print to the same page, thus saving paper.
Thanks.

Simon Lloyd
05-15-2008, 08:44 AM
So you want data off one sheet put in to another for printout? can you supply a dummy workbook of data so we can see your layout and what you would like to achieve?

aalphons
05-15-2008, 10:22 AM
Here is the workbook (with dummy data).
I added a sheet that shows how I want "Report1" to look like.

Thanks for your help in advance and I appreciate any other tips you may have.