Possible Macro to read and change name whilst printing certificate

[DaZ]

Hi All,



Hope your all well

I have been given a task of creating a certificate (which isn't the problem)

The problem I have is I need to assign a signature to it

And also I need it so that where the Name is It will read from a list of names

So when printing the certificate it will print lets say 10 certificates but the name will change on each one without me having to go through typing names it would just read from a list of names

Is this possible?

Any help would be much appreciated many thanks

[excelledsoftware]

Very possible. The names would be stored in a list that would be referenced. Then we would write a loop to change the name and then print out the certificate. This would then be changed to the next name, printed etc. Could you post a sample workbook and this should be no problem.

Thanks

[DaZ]

That's great many thanks for reply

I have attached a quick layout couldn't add images etc and detail yet as 500kb limit on upload

Many thanks again

[excelledsoftware]

No problem,

With a code that is going to automatically print out 29+ reports you will want to tap through it with F8 while you see how and if it works.

Give this a try and let us know how it works.

Option Explicit
Sub ChangeNameThenPrint()
  Dim wb As Workbook, cws As Worksheet, nws As Worksheet
  Dim CheckRow As Integer, TotalNames As Integer

  Set wb = ThisWorkbook
  Set cws = wb.Worksheets("Certificate")
  Set nws = wb.Worksheets("Names")

  TotalNames = nws.Range("A50000").End(xlUp).Row
    
  For CheckRow = 1 To TotalNames
    cws.Range("B42").Value = nws.Range("A" & CheckRow).Value
    cws.PrintOut  'You can set specific printer properties with a ( after Printout
  Next CheckRow

End Sub

[DaZ]

This worked great thank you

Just 1 more thing is it possible to list all names and add a filter so I can just filter out the names I need to print?

Thanks again much appreciated

Originally Posted by excelledsoftware

No problem,

With a code that is going to automatically print out 29+ reports you will want to tap through it with F8 while you see how and if it works.

Give this a try and let us know how it works.

Option Explicit
Sub ChangeNameThenPrint()
  Dim wb As Workbook, cws As Worksheet, nws As Worksheet
  Dim CheckRow As Integer, TotalNames As Integer

  Set wb = ThisWorkbook
  Set cws = wb.Worksheets("Certificate")
  Set nws = wb.Worksheets("Names")

  TotalNames = nws.Range("A50000").End(xlUp).Row
    
  For CheckRow = 1 To TotalNames
    cws.Range("B42").Value = nws.Range("A" & CheckRow).Value
    cws.PrintOut  'You can set specific printer properties with a ( after Printout
  Next CheckRow

End Sub

[excelledsoftware]

Yup pretty easily. How about if you put a 1 or an x in column B next to the name. The code would now be this.

Option Explicit
Sub ChangeNameThenPrint()
  Dim wb As Workbook, cws As Worksheet, nws As Worksheet
  Dim CheckRow As Integer, TotalNames As Integer

  Set wb = ThisWorkbook
  Set cws = wb.Worksheets("Certificate")
  Set nws = wb.Worksheets("Names")

  TotalNames = nws.Range("A50000").End(xlUp).Row
    
  For CheckRow = 1 To TotalNames
    If nws.Range("B" & CheckRow).Value <> "" Then
      cws.Range("B42").Value = nws.Range("A" & CheckRow).Value
      cws.PrintOut  'You can set specific printer properties with a ( after Printout
    End If
  Next CheckRow

End Sub