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



## Carla carla (Dec 31, 2022)

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 (Dec 31, 2022)

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (*not a picture*) of your sheet.  Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).


----------



## John_w (Dec 31, 2022)

This question has already been asked in original thread -









						VBA to cycle through drop down list and save as pdf with file name
					

Hello, I need help on saving my drop down list to individual pdf file with file name.




					www.mrexcel.com


----------



## Carla carla (Dec 31, 2022)

John_w said:


> This question has already been asked in original thread -
> 
> 
> 
> ...


Yes, but I need the filename to be the name not the ID as it is so diffcult.


----------



## Carla carla (Dec 31, 2022)

mumps said:


> It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (*not a picture*) of your sheet.  Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de
> 
> 
> mumps said:
> ...


----------



## Carla carla (Dec 31, 2022)

So this is my file, the ID  is the data validation list and it cycle through all the data for each report. I want the file name to be the name instead of the ID,is that possible?


----------



## mumps (Dec 31, 2022)

It is hard to work with a picture.  Please follow the instructions in Post #2.


----------



## Carla carla (Dec 31, 2022)

mumps said:


> It is hard to work with a picture.  Please follow the instructions in Post #2.


I am trying but it doesnt work for me can I send it via email?


----------



## mumps (Dec 31, 2022)

Try Dropbox.com or Box.com


----------



## Carla carla (Dec 31, 2022)

mumps said:


> Try Dropbox.com or Box.com











						sample 2 (3).xlsb
					

Shared with Dropbox




					www.dropbox.com
				




Here is my file, thank you.


----------



## Carla carla (Dec 31, 2022)

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 (Dec 31, 2022)

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 (Dec 31, 2022)

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 (Jan 6, 2023)

John_w said:


> 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:
> 
> ...


This works super perfect! Thank you so much


----------

