Automating Printing Process

tthiele

Board Regular
Joined
Jun 17, 2002
Messages
63
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,694
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

Board Regular
Joined
Nov 23, 2015
Messages
193
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
Why not just use mail merge? It’s what it’s for - you’ll need to remove the blank line between each student though
 

Forum statistics

Threads
1,081,678
Messages
5,360,472
Members
400,588
Latest member
SpannersWatson

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top