Print bulk certificates

[Dandada2602]

I have played around with some VBA options but I’m not able to get what I’m looking for.

I need to print individual certificates in PDF format, from the list within the same excel file. Each line in the list should be an individual certificate. The resulting PDF file naming should have the certificate number, container number and voyage number. I would like to keep the list as a consecutive, to ensure we don’t duplicate certificate numbers. This will require that the line is marked as print completed, and new lines as print pending. If this is too much to ask for, please omit.

This is the list:

Certificate #	Container #	Line Operator	Booking	Completion Time	Transport Company	Voyage #
CERT-00001	ABCU1234567	ABC	RES-56789	2020/10/18 04:59	Blue Point	202058001
CERT-00002	ABCU1234568	ABC	RES-56790	2020/10/17 21:59	Blue Point	202058001
CERT-00003	ABCU1234569	ABC	RES-56791	2020/10/18 03:59	Blue Point	202058001
CERT-00004	ABCU1234570	ABC	RES-56792	2020/10/18 02:59	Green Point	202058001
CERT-00005	ABCU1234571	ABC	RES-56793	2020/10/18 03:59	Green Point	202058001
CERT-00006	ABCU1234572	ABC	RES-56794	2020/10/18 19:59	Green Point	202058001
CERT-00007	ABCU1234573	BCC	RES-56795	2020/10/17 06:59	Green Point	202058001
CERT-00008	ABCU1234574	BCC	RES-56796	2020/10/17 07:59	Red Point	202058001
CERT-00009	ABCU1234575	BCC	RES-56797	2020/10/17 18:59	Red Point	202058001
CERT-00010	ABCU1234576	BCC	RES-56798	2020/10/19 09:59	Red Point	202058001

 

[cooper645]

Ok,

I have made some assumptions and additions.
Assumptions:
formatting for dates etc has been applied to the certificate sheet
the cert sheet info is centered across selection in the rows above table from C:F
Additions:
Data sheet got a new set of info entered into Column H, titled Print with "Pending" entered in all lines.
(the code will amend pending to complete once done)

You will need to amend a couple of variables to suit your sheet names and file path where certs should be saved.

Option Explicit

Sub PrintCerts()
Application.ScreenUpdating = False
On Error GoTo Err
Dim Cert As Worksheet, Data As Worksheet
Dim LRow As Long, i As Double, svNm As String, fPath As String

Set Cert = Sheets("Certificate") 'Amend for name of certificate tab
Set Data = Sheets("Data") 'Amend for name of sheet containing your list
fPath = "/Users/cooper645/Desktop/" 'Set the filepath for where the certs should be saved"

LRow = Data.Cells(Rows.Count, "A").End(xlUp).Row 'Find the last row of data

For i = 2 To LRow
    If Data.Cells(i, 8).Value = "Pending" Then
        Cert.Range("C2").Value = Data.Cells(i, 3) 'cert heading
        Cert.Range("C8").Value = Data.Cells(i, 1) 'Cert Number
        Cert.Range("C12").Value = Data.Cells(i, 3) & " Certify the scan:" 'Certify line
        Cert.Range("C15").Value = Data.Cells(i, 2) 'Container
        Cert.Range("D15").Value = Data.Cells(i, 4) 'Booking
        Cert.Range("E15").Value = Data.Cells(i, 5) 'Completed
        Cert.Range("F15").Value = Data.Cells(i, 7) 'Voyage Number
        Cert.Range("A18").Value = "As requested by company " & Data.Cells(i, 6) & " we proceed to scan" 'Transport Company"
        Cert.Range("C22").Value = Date 'Todays date
        Cert.Range("A28").Value = "Company: " & Data.Cells(i, 3) 'Company certifying
        
            svNm = Data.Cells(i, 1) & "_" & Data.Cells(i, 2) & "_" & Data.Cells(i, 7) & ".pdf" 'Create the filename
            
            'Save PDF
            Application.PrintCommunication = True
            ChDir fPath
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=fPath & svNm, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            
                Data.Cells(i, 8).Value = "Complete" 'Mark row as completed
            
    End If
Next i

Err:
Application.ScreenUpdating = True
End Sub