Print from result of Data validated dropdown list to one combined PDF file

[stevew303]

Hi All,

I have an excel file for pupil timetables. In cell "C1" i have a dropdown menu which changes to each pupil name. Below this is the pupils timetable for the week. I have created a macro that automatically changes the dropdown to the next pupil and then prints the pupil timetable, then changes to the next pupil and prints again all from Excel to a printer. If I change my default printer to PDF it does the same but creates a new file for each time it changes the name. I would like the PDF to print all the dropdowns to one PDF file containing every pupil timetable in it please.

My VBA looks like this:

Sub PrintAll()
Dim R As Range
For Each R In Sheets("Hidden Info").Range("B2:B132")
If Not IsEmpty(R) Then
Range("B1") = R
ActiveSheet.PrintOut
End If
Next
End Sub


"Hidden info" is the tab where I have the full list of names in the range "B2:B132" (This all works fine)
"B1" is where I have the drop down of pupil names that changes from one to another.

I hope this is clear as it is my first post!

Thanks

Steve