Print out Certificates from list

[rfletcher35]

Ok hope I explain this well.

I have a list of details over 4 columns, each row contains 4 bits of information.
On another sheet I have an image of my certificate that has 4 section for this data to go.
I have a routine setup already that compiles this data.
What I am wanting is to print off these certificates, so it looks at the first row in the case A2, then it puts that info on the certificate in the right place. It does this along with data in B2, C2 and D2. It will then print the certificate and then go to A3 and so on until the data is no more i.e. blank cell in column A.

Any ideas?

Fletch

 

[portews]

Try this:

Option Base 1

Sub certificate()
[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/980866-print-out-certificates-list.html[/COLOR]
Dim CertCells()
Dim i As Integer
Dim j As Integer
[COLOR=#00ff00]'set the cells on the Certificate for ranges A thru D[/COLOR]
CertCells = Array("K23", "K28", "D41", "F41")


[COLOR=#00ff00]'set the print range[/COLOR]
Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$U$47"
[COLOR=#00ff00]'choose the printer[/COLOR]
bOK = Application.Dialogs(xlDialogPrinterSetup).Show
     If bOK = False Then Exit Sub

[COLOR=#00ff00]'for each name on the list[/COLOR]
For i = 2 To Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
[COLOR=#00ff00]    'for each column in the data list[/COLOR]
    For j = 1 To UBound(CertCells)
[COLOR=#00ff00]        'clear the old info[/COLOR]
        Sheets("Sheet1").Range(CertCells(j)).ClearContents
[COLOR=#00ff00]        'copy each column per row to the place on the certificate[/COLOR]
        Sheets("Sheet1").Range(CertCells(j)) = Sheets("Sheet2").Cells(i, j)
    Next
[COLOR=#00ff00]    'print out the sheet[/COLOR]
    Sheets("Sheet1").PrintOut Copies:=1, Collate:=True
Next
End Sub