how to set filename for data validation list.

[Carla carla]

Hello,

The below is the macro code I am using to create pdf files from data validation list. My data validation is on cell B2. But I want the file name to save as A2. How can I add this condition in the existing macro?

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

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: how to set filename for pdf
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[DanteAmor]

Change this:

With dataValidationCell.Worksheet.Range("A1:I45")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

For this:

    With dataValidationCell.Worksheet.Range("A1:I45")
      .ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=destinationFolder & dvValueCell.Value & "_" & Worksheets("test1").Range("A2").Value & ".pdf", _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With

Or this:

    With dataValidationCell.Worksheet.Range("A1:I45")
      .ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=destinationFolder & Worksheets("test1").Range("A2").Value & ".pdf", _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With