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

daikeda

New Member
Joined
Apr 30, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,029
Which is the data validation dropdown cell? This macro assumes B2.
VBA Code:
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
 

Forum statistics

Threads
1,143,619
Messages
5,719,769
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top