cycle through data validation list to print pdf with a file name

[Carla carla]

Hello, at the moment I have a an excel report for 500 kids. The execl report is then populated to the pdf.
I have created a data validation list using the ID to string name, marks and other details.
When I print to pdf the file name appears to be the student ID.
Is there a way for me to set a file name for each file example the student name instead of ID?

 

[mumps]

Try:

Sub Do_It()
    Application.ScreenUpdating = False
    Dim pdf_path As String, srcWS As Worksheet, rng As Range
    Set srcWS = Sheets("Sheet2")
    pdf_path = ThisWorkbook.Path & Application.PathSeparator
    With srcWS
        For Each rng In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
            Range("J8") = rng
            Range("D8") = rng.Offset(, 1)
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdf_path & Range("D8") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next rng
    End With
    Application.ScreenUpdating = True
    MsgBox "Done", vbInformation, "All"
End Sub

 

[John_w]

Or, as I said in your original thread, if you have a VLOOKUP formula which looks up the data validation value (ID in J8) in a table of IDs and names and returns the name (in cell D8) related to the ID, then it's better for the user and the macro can also read the D8 cell values to use as the PDF file name.

Add this formula to D8:

=VLOOKUP(J8,Sheet2!A3:B13,2,FALSE)

This macro loops through the data validation values in J8 and saves the PDFs with the name in D8.

Public Sub Create_PDFs()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
   
    destinationFolder = ThisWorkbook.Path & "\"     'Same folder as workbook containing this macro
    'destinationFolder = "C:\path\to\folder\"        'Or specific folder
   
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
        
    'Cell containing data validation in-cell dropdown
   
    Set dataValidationCell = Worksheets("MATHS").Range("J8")
    
    'Source of data validation list
   
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
    
    'Create PDF for each data validation value, using cell D8 as the file name
   
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With Worksheets("MATHS")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & .Range("D8").Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
       
End Sub

 

[Carla carla]

Or, as I said in your original thread, if you have a VLOOKUP formula which looks up the data validation value (ID in J8) in a table of IDs and names and returns the name (in cell D8) related to the ID, then it's better for the user and the macro can also read the D8 cell values to use as the PDF file name.

Add this formula to D8:

=VLOOKUP(J8,Sheet2!A3:B13,2,FALSE)

This macro loops through the data validation values in J8 and saves the PDFs with the name in D8.

Public Sub Create_PDFs()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
  
    destinationFolder = ThisWorkbook.Path & "\"     'Same folder as workbook containing this macro
    'destinationFolder = "C:\path\to\folder\"        'Or specific folder
  
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
       
    'Cell containing data validation in-cell dropdown
  
    Set dataValidationCell = Worksheets("MATHS").Range("J8")
   
    'Source of data validation list
  
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
   
    'Create PDF for each data validation value, using cell D8 as the file name
  
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With Worksheets("MATHS")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & .Range("D8").Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
      
End Sub

This works super perfect! Thank you so much