Macro to print pdf report of 30 diff students

[Dee]

I have created a report for a report card. Student's name is stored in sheet2 and report is present in sheet 1. cell C3 contains Students name. I have 30 students and what i need is C3 should change automatically by using list f 30 students in sheet 2 and generate 30 different reports and save it in a folder. I wrote codes for second part but got no idea about the 1 part. Can any one help me on this pls...


second part code goes like this...


Sub Make_PDF()


Dim pdfName As String

Dim spath As String

pdfName = Range("C3").Text

spath = "D:my folderIndividual reports" & fname

fname = spath & pdfName + " Weekly Update " & Format$(Date, "mm-dd-yyyy") + ".pdf"


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox ("Files saved in your drive")


End Sub


Thanking you in advance,

Dee

 

[Luke M]

I'm not sure where the exact list of names is, but you should be able to do something like this:

[pre]

Sub Make_PDF()

Dim pdfName As String
Dim spath As String
Dim StudentList As Range

'Define where the list of names are
Set StudentList = Worksheets("Sheet2").Range("A1:A30")

'Now we loop through the list
For Each c In StudentList
'Set the cell value
Range("c3") = c.Value

'print it!
pdfName = Range("C3").Text
spath = "D:my folderIndividual reports" & fname
fname = spath & pdfName + " Weekly Update " & Format$(Date, "mm-dd-yyyy") + ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
'MsgBox ("Files saved in your drive")
Next c

End Sub

[/pre]

 

[Dee]

Hey Luke,

Thank you very much for the reply.

Only issue here is ... Students name is in Worksheets("Sheet2").Range("A1:A30") where as report template which needs to be printed is in Sheet1 Print_Area. Names from A1 to A30 should get copied in Worksheets("Sheet1").Range("C3").


Can you please help me with this?


Thank you very much,


Dee

 

[Dee]

Hey Luke, I fixed it...thanks a millions for your support...

My final code...

Sub Make_Individualreport()


Dim pdfName As String

Dim spath As String

Dim stlist As Range


'Define where the list of names are

Set stlist = Worksheets("Prj Managers").Range("A1:A30")


'Now we loop through the list

For Each c In stlist

'Set the cell value

Worksheets("Template").Range("c3") = c.Value


'print it!

pdfName = Worksheets("Template").Range("C3").Text

spath = "D:sdeepaIndividual reports"

fname = spath & pdfName + " Weekly Update " & Format$(Date, "mm-dd-yyyy") + ".pdf"


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

'MsgBox ("Files saved in your drive")

Next c


End Sub

 

[Luke M]

Awesome, glad you got it figured out!