Printing grade sheet (one for each student) from list

[immyjimmy]

Greetings, all...

Column AA1 has a list of student's names (it varies each term). Cell B9 has been a drop down list which when one was selected, updated vie vlookup the student's scores in each subject. Now I want to change it so batch print from the list. It prints the first student's sheet fine, but won't go to the next...

Here's my code.

Sub Print_Page()
Dim COUNTER As Integer
COUNTER = 1
Range("AA1").Select
Selection.Copy
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1:F36").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("AA1").Select
ActiveCell.Offset([COUNTER], [0]).Range("A1").Select
COUNTER = COUNTER + 1
Do While B9 <> ""
Selection.Copy
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1:F36").Select
Selection.PrintOut Copies:=1, Collate:=True
Loop
Range("A1").Select
End Sub

Any ideas on what I need to change/edit/fix?

Thanks in advance,
Jim

The most exciting phrase to hear in science, the one that heralds new discoveries, is not Eureka! (I found it!) but rather, 'hmm.... that's funny...' - Isaac Asimov

 

[Smitty]

Does this do it for you:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Print_Page()<br>    <SPAN style="color:#00007F">Dim</SPAN> COUNTER <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> COUNTER = 1 <SPAN style="color:#00007F">To</SPAN> Cells(Rows.Count, "AA").End(xlUp).Row<br>            Range("AA" & COUNTER).Copy Range("B9")<br>            Range("A1:F36").PrintOut Copies:=1, Collate:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> COUNTER<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[immyjimmy]

Smitty,

Thanks, so much. You got me on the right track. The difficulty was in getting it to stop printing when the names stopped. There can be up to 25 students per class. Their names are entered onto another sheet. Column AA was an IF/VLOOKUP combo which returned the name if it was there and a blank if it was none. I put a counter (COUNT) in Y1 and changed your

For COUNTER = 1 To Cells(Rows.Count, "AA").End(xlUp).Row

to

For COUNTER = 1 To COUNT

and viola!

Here (for posterities sake) is my final code:

Sub Print_Page()
    Dim COUNTER As Long
    Dim COUNT As Integer  ' WHY NOT SET BOTH TO LONG?
    COUNT = Range("Y1")
        
        For COUNTER = 1 To COUNT
            Range("AA" & COUNTER).Copy Range("B9")
            Range("A1:F36").PrintOut Copies:=1, Collate:=True
        Next COUNTER
Range("A1").Select
End Sub

Again, thanks so much!

Jim

Celebrate Freedom... read a banned book.