Automating Printing Process

[tthiele]

I have taken over a position for a colleague who used to do the following:

One sheet has a list of examinations a student is signed up to take. Student A might have three rows for three different tests, Student B might have two rows for two different tests, Student C might have six rows for six different tests, etc. There is a blank row between each of these groupings of students. There is a second sheet that has a form letter of sorts that is printed and sent to the student to confirm the list of examinations is correct.

My predecessor would manually copy each student's information into the sheet with the form letter, print, and repeat for nearly 600 students.

Is there a relatively easy way to automate this?

 

[SpillerBD]

Access has better facilities for this....
In Excel, you could force your "letter" into the Header and Footer areas. You can also use the Print Header Rows for additional text information, including Text Box(es).
Then on the list of examinations, remove your blank lines and sort by Students. Then use the Subtotals feature to add Page breaks.

 

[Lukums]

I have taken over a position for a colleague who used to do the following:

One sheet has a list of examinations a student is signed up to take. Student A might have three rows for three different tests, Student B might have two rows for two different tests, Student C might have six rows for six different tests, etc. There is a blank row between each of these groupings of students. There is a second sheet that has a form letter of sorts that is printed and sent to the student to confirm the list of examinations is correct.

My predecessor would manually copy each student's information into the sheet with the form letter, print, and repeat for nearly 600 students.

Is there a relatively easy way to automate this?

Without seeing the sheet it's really hard BUT.
Is this possible absolutely.

What you need to do is: Find the determining factor of when the persons name changes, I'd array this information send to a blank sheet as a group PER PERSON and then do the below PRINT.
Go back to original sheet array the next student - copy - paste to another sheet and the below on a FOR NEXT LOOP.
I'm quite the novice but it's how we printed out 200 jobs per week automatically (similar to what you're trying to do)

Dim WshNetwork
Set WshNetwork = CreateObject("WScript.Network")


Sheets("NAME").Select


WshNetwork.SetDefaultPrinter "PRINTER NAME"
ActiveSheet.PrintOut ActivePrinter:="PRINTER NAME" '''' will tell the printer to PRINT

 

[Norie]

Where do the rows data for the student go on the form letter?

What other data needs to be transferred over for each student?

 

[Kyle123]

Why not just use mail merge? It’s what it’s for - you’ll need to remove the blank line between each student though