Print multiple files by cycling through names in dropdown list

[chareaves]

I'm assuming this will need some kind of macro (but I really don't know) so I've posted it here. I'm using Excel 2016.

On my spreadsheet I have a range, say, A172:F179.
A172 contains a dropdown, which has names in a class.
When I select a different name, the info in the other cells in the range changes automatically.

What I've been doing is selecting one name at a time, then selecting the range, then going to "Print", then printing a pdf. I repeat this for every student in the class.

The pdf gets named as follows:
StudentLastName StudentFirstName - SubjectName - AssessmentTitle.pdf

eg. Gates Bill - Year 10 Maths - Indices Test.pdf

I would like to know if there's a way I can automate this process, presumably by using a macro. I know what macros are but I'm not super-skilled with them.

To be clear, the end result would be a folder (which already exists) with pdf files (one for each student). Each file would be correctly named, and when it was opened it would show the information which was on the range corresponding to that name. I'd be really grateful for any help or pointers. Thanks.

[dflak]

The requirement seems to be stated clearly, however I don't think you will get many takers since it takes time to set up the workbook. Please provide a sample and we can better help you.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[chareaves]

OK, well after doing a lot of research and fiddling around, I managed to make this work. I'm posting it here in case others want to know how. I'm sure it's not the most elegant or efficient code but it does the job perfectly. I don't know how to make the title say "Solved" now.

Sub PrintWhileLoop()

Dim FullName As String
Dim c As Range
Dim MyList As Range
Dim DropRange As Range
Dim wsPrint As Range
Dim TaskName As String
Dim MyPathName As String
Dim MySheetName As String
Dim MyWorkbookName As String

MyPathName = Application.ActiveWorkbook.Path
MySheetName = ActiveWorkbook.ActiveSheet.Name
MyWorkbookName = Replace(ThisWorkbook.Name, ".xlsm", "")

'Where is list of names?
Set MyList = Worksheets("Students").Range("F2:F26")

'Where is dropdown?
Set DropRange = ActiveWorkbook.ActiveSheet.Range("A172")

'What is the name of the task?
TaskName = ActiveWorkbook.ActiveSheet.Range("A1") & " " & ActiveWorkbook.ActiveSheet.Range("C1")

'Which sheet to print?
Set wsPrint = ActiveWorkbook.ActiveSheet.Range("A172:F179")

'No dialog boxes during printing
Application.ScreenUpdating = False

'Make feedback folder if it doesn't exist
'Make the folder for the assessment task within feedback if it doesn't exist
On Error Resume Next
MkDir MyPathName & "\" & MyWorkbookName
MkDir MyPathName & "\" & MyWorkbookName & "\Feedback\"
MkDir MyPathName & "\" & MyWorkbookName & "\Feedback\" & TaskName
On Error GoTo 0

'Loop through each name
For Each c In MyList
    FullName = c.Value
    DropRange.Value = FullName
    
    'Extract first and last name from the full name, so we can name the pdf with last name first
    Dim FirstName As String
    Dim LastName As String
    Dim SpacePos As Integer
    
    SpacePos = InStr(FullName, " ")
    FirstName = Left(FullName, SpacePos - 1)
    LastName = Right(FullName, Len(FullName) - Len(FirstName))
    
    'Save a correctly named pdf file for each student

   wsPrint.ExportAsFixedFormat xlTypePDF, MyPathName & "\" & MyWorkbookName & "\Feedback\" & TaskName & "\" & LastName & ", " & FirstName & " - " & MyWorkbookName & " - " & TaskName & ".pdf", , , , , , False
Next

'Turn back on any dialog boxes
Application.ScreenUpdating = True

End Sub