cycle through data validation list to print to pdf and save as cell text

[daikeda]

Hi,
I have:
- worksheet name("test1")
- data validation list in "A"
- print range is first page of worksheet or A1: I45

looking if there's a way to have macro that cycles through the data validation list and prints a PDF with the name of the text in that cell and loop through so every item of list prints as their own pdf. aka if there's 100 lines in the drop down, then i'd end with 100 pdfs each individually named for the 100 drop down options.

 

[John_w]

Which is the data validation dropdown cell? This macro assumes B2.

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("test1").Range("B2")
     
    'Source of data validation list
    
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
     
    'Create PDF for each data validation value
    
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With dataValidationCell.Worksheet.Range("A1:I45")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
        
End Sub

 

[rayzuki]

Which is the data validation dropdown cell? This macro assumes B2.

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("test1").Range("B2")
    
    'Source of data validation list
   
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
    
    'Create PDF for each data validation value
   
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With dataValidationCell.Worksheet.Range("A1:I45")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
       
End Sub

Hello sir.. this code work in my worksheet, i really apreciate it and really thanks you, this code make my work better faster than before.
and can i ask about how to make remove blank page in this code.

 

[John_w]

how to make remove blank page in this code.

There shouldn't be any blank pages if you're using exactly the same code and the width of columns A:I (range A1:I45) is 1 page wide.

It's best if you start a new thread, posting your code and worksheet data (using XL2BB - Excel Range to BBCode) and describe exactly where the blank page is occurring.