0

I have a data validation list with names of students in cell C5. Every time I bring up a name it fills in a report on that page with results for that particular student. (it uses vlookup in the report). I need to print these reports for all students. I currently run a macro that copies the names picking it from a student list on a different sheet and then prints. I want the macro to run through the validation list and print automatically and keep going till the last name in the list. Below is what I am currently using.

Sub PrintAll() ' ' PrintAll Weekly Tests Report Macro '

Range("C5").Select
Sheets("StudentsOne").Select
Range("A3").Select
Selection.Copy
Sheets("Test Report1").Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

Range("C5").Select
Sheets("StudentsOne").Select
Range("A4").Select
Selection.Copy
Sheets("Test Report1").Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

Range("C5").Select
Sheets("StudentsOne").Select
Range("A5").Select
Selection.Copy
Sheets("Test Report1").Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

and it keeps going like this according to how many studetns there are. It changes every year and I update it. It ranges between 35 and 50. So every year I either delete or add more ranges to copy paste. I would love to have the macro just run through the list himself end execute the printing. right now there are 41 records in the list. (41 names) Any help appreciated. Thanks

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Marleen
  • 15
  • 4
  • So in effect you are printing a sheet with just A1 filled in? – SJR Nov 09 '18 at 10:40
  • First read [how to avoid using .Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) ; it will improve your code quality and speed of execution. – QHarr Nov 09 '18 at 10:41
  • No C5 is filled in. It says Student name: and then in C5 there is the data validation list. In the rows below there is a report that gets filled in with vlookup picking up the results matching the name in C5. It is only in C5 that the data needs to be changed and the rest changes automatically. But it prints a whole sheet with information not just one cell. – Marleen Nov 09 '18 at 10:45
  • i would change your code to be something like this also `Sheets("Test Report1").Range("C5").value=Sheets("StudentOne").range("a4").value` – Nathan_Sav Nov 09 '18 at 10:52
  • 1
    How is the validation set up, a list typed in by you or a range? – Nathan_Sav Nov 09 '18 at 10:52
  • I think it is a range. The validation refers to =Students!$A$3:$A$47 – Marleen Nov 09 '18 at 10:58
  • How do you populate the data validation list in `C5`?. Also, your code references two sheets `StudentsOne` and `Test Report1`. What is the role of each of them? – Alex P Nov 09 '18 at 11:18
  • What do you mean how I populate it? I click on the drop down to pick a name from the list. But in the macro because I don't know how to make it go through the list I copy and paste each name from the StudentsOne sheet which contains my list with student names. It's also used to reference the data validation list from. Otherwise I have to pick a name then print and do this 41 times. Test Report1 is the sheet with the report layout where I use the data validation list to bring up each student's name to see her report. It's the sheet that needs to be printed 41 times for each student's results. – Marleen Nov 09 '18 at 11:43

2 Answers2

0

You could try following a pattern like this:

Sub PrintAll()
    Dim Students As Range, student As Range

    Set Students = Worksheets("StudentList").Range("A1:A40")

    For Each student In Students
        Worksheets("StudentsOne").Range("C5") = student
        Worksheets("Test Report1").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    Next student
End Sub

Assumptions:

  1. You have list of students on a worksheet (StudentList) to populate the data validation
  2. The sheet StudentsOne is where you want to paste the student name (i.e. C5)
  3. The actual sheet to be printed is Test Report1

Notes:

Each time you come to run the code you just need to adjust the Students range reference. If you have 50 students one year then change the range reference to A1:A50

Alex P
  • 12,249
  • 5
  • 51
  • 70
0

As the comments suggest, your question is not very clearly worded. This is my best guess.

Sub PrintAll() ' ' PrintAll Weekly Tests Report Macro '

Dim r As Range

With Sheets("StudentsOne")
    For Each r In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
        Sheets("Test Report1").Range("C5").Value = r.Value
        Sheets("Test Report1").PrintOut Copies:=1, Collate:=True, ignorePrintAreas:=False
    Next r
End With

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • I tried this. I think it works. I don't have a printer where I am now so I had to save as xps in order to view the print but I couldn't be bothered to do this 41x. It worked for the first 2 when I go to the office I will try it. Thanks for your help. It is a huge timesaver. – Marleen Nov 09 '18 at 11:55