Certificate Printing Macro

[Laurenwsjassoc]

Hello,

I need to figure out how to write a macro that will take info from a worksheet (a row that goes across many columns) and place that info in another worksheet that is a certificate template. This macro also needs to recognize when it hits the last filled in column in a row it should print the certificate, move to the next row replace the info in the cert with the date in the next row and then print and keep doing that until it gets to an empty row.

Is this sort of macro possible? If so how do I go about creating it. I know it seems confusing I am happy to answer any questions to clarify.

Thank you!

 

[Laurenwsjassoc]

How do you do this so fast???

I am getting an error message for line:
If Range("AK" & i).Value <> "" And Range("<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:State w:st="on">AL</st1:State></st1lace>").Value <> "" Then<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
wsCert.PrintOut From:=1, To:=1<o></o>
End If<o></o>


It is bolded is both places

 

[MrKowz]

How do you do this so fast???

I code a lot. It's all practice and research.

Try:

Public Sub PrintAllCertificates()
Dim i       As Long, _
    LR      As Long, _
    wsData  As Worksheet, _
    wsCert  As Worksheet, _
    trial   As VbMsgBoxResult
 
 
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
Set wsData = Sheets("Data")
Set wsCert = Sheets("Certificate")
LR = wsData.Range("A" & Rows.Count).End(xlUp).Row
trial = MsgBox("Would you like to run a trial to" & vbLf & "ensure certificate prints properly?", vbYesNo)
If trial = vbNo Then
    For i = 12 To LR
        Application.StatusBar = "Currently printing row " & i & " of " & LR
        wsData.Range("Z1").Value = i
        Application.Calculate
        If Range("AK" & i).Value <> "" And Range("AL" & i).Value <> "" Then
            wsCert.PrintOut From:=1, To:=1
        End If
    Next i
Else
    For i = 12 To 15
        Application.StatusBar = "Currently printing row " & i & " of 15 (TRIAL RUN)"
        wsData.Range("Z1").Value = i
        Application.Calculate
        If Range("AK" & i).Value <> "" And Range("AL" & i).Value <> "" Then
            wsCert.PrintOut From:=1, To:=1
        End If
    Next i
End If
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
End Sub

 

[Laurenwsjassoc]

Would you mind highlighting for me in the print macro where it instructs to end print or where it instructs exactly what to print. For some reason now it is not printing the last employee for one of the spreadsheets I am using it on. I figure if I understood the print all certs macro a little better I could fiddle with it and not have to bother you...

 

[MrKowz]

Here is some heavily commented code that should help understand it a bit better:

Public Sub PrintAllCertificates()
Dim i       As Long, _
    LR      As Long, _
    wsData  As Worksheet, _
    wsCert  As Worksheet, _
    trial   As VbMsgBoxResult
 
' Application Initialization
'    Turns off various Application methods to optimize processing speed and effeciency
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
' Variable Initialization:
'    Defines wsData and wsCert to be the data worksheets and the certificate worksheets
'    Defines LR to be the last row of the data
Set wsData = Sheets("Data")
Set wsCert = Sheets("Certificate")
LR = wsData.Range("A" & Rows.Count).End(xlUp).Row
' Trial Check
'    Asks if you want to run a trial to ensure certificates print properly.
'        Yes: Trial will print 4 records
'        No: Trial will print all records
trial = MsgBox("Would you like to run a trial to" & vbLf & "ensure certificate prints properly?", vbYesNo)
If trial = vbNo Then
    ' Record Loop
    '    Loops through all records and changes the value in Z1 to populate the Certificate sheet via INDIRECT formulas
    For i = 12 To LR
        Application.StatusBar = "Currently printing row " & i & " of " & LR
        wsData.Range("Z1").Value = i
        Application.Calculate
        ' Valid Record Check
        '    Checks to see if there is a value in columns AK and AL.  If there isn't the record doesn't print
        If Range("AK" & i).Value <> "" And Range("AL" & i).Value <> "" Then
            ' Print Certificate
            '    Prints the certificate worksheet
            wsCert.PrintOut From:=1, To:=1
        End If
    Next i
Else
    ' Record Loop
    '    Loops through all records and changes the value in Z1 to populate the Certificate sheet via INDIRECT formulas
    For i = 12 To 15
        Application.StatusBar = "Currently printing row " & i & " of 15 (TRIAL RUN)"
        wsData.Range("Z1").Value = i
        Application.Calculate
        ' Valid Record Check
        '    Checks to see if there is a value in columns AK and AL.  If there isn't the record doesn't print
        If Range("AK" & i).Value <> "" And Range("AL" & i).Value <> "" Then
            ' Print Certificate
            '    Prints the certificate worksheet
            wsCert.PrintOut From:=1, To:=1
        End If
    Next i
End If
' Application Reset
'    Resets all Application methods
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
End Sub

The only reason I can think of it not printing the last employee is if there is no value in AK or AL

 

[Laurenwsjassoc]

There will be instances where AK and AL are empty on the last employee so should the code be adjusted? If it is empty I'd like those lines to be omitted for that particular employee but for the cert to still print. Actually you are right for this spread sheet those cells are empty for the last employee

 

[MrKowz]

There will be instances where AK and AL are empty on the last employee so should the code be adjusted? If it is empty I'd like those lines to be omitted for that particular employee but for the cert to still print. Actually you are right for this spread sheet those cells are empty for the last employee

By Omitted, do you mean you want those fields on the certificate to just show blank?

 

[Laurenwsjassoc]

Yes that is exactly what I mean!

 

[MrKowz]

This should do it for you.

Public Sub PrintAllCertificates()
Dim i       As Long, _
    LR      As Long, _
    wsData  As Worksheet, _
    wsCert  As Worksheet, _
    trial   As VbMsgBoxResult
 
' Application Initialization
'    Turns off various Application methods to optimize processing speed and effeciency
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
' Variable Initialization:
'    Defines wsData and wsCert to be the data worksheets and the certificate worksheets
'    Defines LR to be the last row of the data
Set wsData = Sheets("Data")
Set wsCert = Sheets("Certificate")
LR = wsData.Range("A" & Rows.Count).End(xlUp).Row
' Trial Check
'    Asks if you want to run a trial to ensure certificates print properly.
'        Yes: Trial will print 4 records
'        No: Trial will print all records
trial = MsgBox("Would you like to run a trial to" & vbLf & "ensure certificate prints properly?", vbYesNo)
If trial = vbNo Then
    ' Record Loop
    '    Loops through all records and changes the value in Z1 to populate the Certificate sheet via INDIRECT formulas
    For i = 12 To LR
        Application.StatusBar = "Currently printing row " & i & " of " & LR
        wsData.Range("Z1").Value = i
        Application.Calculate
        ' Print Certificate
        '    Prints the certificate worksheet
        wsCert.PrintOut From:=1, To:=1
    Next i
Else
    ' Record Loop
    '    Loops through all records and changes the value in Z1 to populate the Certificate sheet via INDIRECT formulas
    For i = 12 To 15
        Application.StatusBar = "Currently printing row " & i & " of 15 (TRIAL RUN)"
        wsData.Range("Z1").Value = i
        Application.Calculate
        ' Print Certificate
        '    Prints the certificate worksheet
        wsCert.PrintOut From:=1, To:=1
    Next i
End If
' Application Reset
'    Resets all Application methods
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
End Sub

 

[Laurenwsjassoc]

We are so close I can see it! It is now printing the last employee with blank AK "$0.00" respectively. I tried to reformat the cells for the blank employees to general formatting...should I format to text and then it will show blank?