Automate switching dropdown values and exporting to PDF

[nlarski]

Hi all,

I am trying to modify a project that I worked on a while ago where a macro would look at each value in a drop down list and export to PDF for each selection. What I am looking to change is adding one more dropdown into the equation.

For dropdown A I have 7 values stored (A1, A2, A3, A4, A5, A6, A7)
For dropdown B I will have 3+ values stored (B1, B2, B3,...this could be more or less based on the selection of Dropdown A)

I'm trying to make the macro select each combination and export them to a specific folder with the name of each file a concatenation of each dropdown selection

File 1:A1B1
File 2: A1B2
File 3: A1B3
File 4: A2B1
File 5:A2B2
File 6: A2B3
continuing for however many selections there are available.


Here is what I have in my current file that only takes into account one dropdown value.

Public Sub Create_PDFs()

Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
Dim dropdownValue As String
Dim PDFfile As String, i As Long
Dim destinationFolder As String
destinationFolder = "C:\Users\ME\Desktop\Export\PDF"
'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("Lookup").Range("B1")
'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
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & " " & dvValueCell.Value & ".PDF", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next
End Sub


Any help in modifying this to accommodate another dropdown layer would be greatly appreciated!

Thanks!

 

[Kenneth Hobson]

Please paste code between code tags. Click the # icon on reply toolbar to insert the tags.

Do another loop inside the other after you Set a range for the other data validation as you did for first.

 

[nlarski]

Apologies for my lack of understanding with VBA..

so I added new ranges for the additional data validation cell, but I'm not sure where the second loop should begin, since it looks like the loop ends after the pdf is made.

Here's what I have so far..using the code tags of course

Public Sub Create_PDFs()
Dim dataValidationCell1 As Range, dataValidationListSource1 As Range, dvValueCell1 As Range
Dim dataValidationCell2 As Range, dataValidationListSource2 As Range, dvValueCell2 As Range
Dim dropdownValue As String
Dim PDFfile As String, i As Long
Dim destinationFolder As String
destinationFolder = "C:\Users\ME\Desktop\Test"
'destinationFolder = "C:\path\to\folder" 'Or specific folder
If Right(destinationFolder, 1) <> "" Then destinationFolder = destinationFolder & ""
'Cell containing data validation in-cell dropdown
Set dataValidationCell1 = Worksheets("Lookup").Range("C2")
Set dataValidationCell2 = Worksheets("Lookup").Range("C4")
'Source of data validation list
Set dataValidationListSource1 = Evaluate(dataValidationCell1.Validation.Formula1)
Set dataValidationListSource2 = Evaluate(dataValidationCell2.Validation.Formula1)
'Create PDF for each data validation value
For Each dvValueCell1 In dataValidationListSource1
dataValidationCell1.Value = dvValueCell1.Value
With dataValidationCell1.Worksheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & " " & dvValueCell.Value & ".PDF", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next
End Sub

 

[Kenneth Hobson]

I don't know your data so I am guessing on this.

Move the 2nd Set to after:

dataValidationCell1.Value = dvValueCell1.Value

Then do your 2nd For Each loop after that. The 2nd Next will go just before the 1st Next.

 

[nlarski]

Thank you so much Kenneth! Works perfectly!

One more (hopefully) quick question. The files are saving to documents instead of the path that I specified. Can you see anything wrong there? If not this isn't that big of a deal.

Thanks again!!

Here's the code in case anyone that stumbles upon this wants to see:

Public Sub Create_PDFs()
Dim dataValidationCell1 As Range, dataValidationListSource1 As Range, dvValueCell1 As Range
Dim dataValidationCell2 As Range, dataValidationListSource2 As Range, dvValueCell2 As Range
Dim dropdownValue As String
Dim PDFfile As String, i As Long
Dim destinationFolder As String
destinationFolder = "C:\Users\ME\Desktop"
'destinationFolder = "C:\path\to\folder" 'Or specific folder
If Right(destinationFolder, 1) <> "" Then destinationFolder = destinationFolder & ""
'Cell containing data validation in-cell dropdown
Set dataValidationCell1 = Worksheets("Lookup").Range("C2")
'Source of data validation list
Set dataValidationListSource1 = Evaluate(dataValidationCell1.Validation.Formula1)
'Create PDF for each data validation value
For Each dvValueCell1 In dataValidationListSource1
dataValidationCell1.Value = dvValueCell1.Value
Set dataValidationCell2 = Worksheets("Lookup").Range("C4")
Set dataValidationListSource2 = Evaluate(dataValidationCell2.Validation.Formula1)
For Each dvValueCell2 In dataValidationListSource2
With dataValidationCell1.Worksheet
With dataValidationCell1.Worksheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Womens" & dvValueCell1.Value & dvValueCell2.Value & ".PDF", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End With
Next
Next
End Sub
<strike></strike>

 

[Kenneth Hobson]

You can use ChDir() before loop but I prefer more literal. e.g.

Filename:=ThisWorkbook.Path & "\Womens" & dvValueCell1.Value & dvValueCell2.Value & ".PDF"

 

[DanteAmor]

check the changes in blue

Public Sub Create_PDFs()
Dim dataValidationCell1 As Range, dataValidationListSource1 As Range, dvValueCell1 As Range
Dim dataValidationCell2 As Range, dataValidationListSource2 As Range, dvValueCell2 As Range
Dim dropdownValue As String
Dim PDFfile As String, i As Long
Dim destinationFolder As String
destinationFolder = "C:\Users\ME\Desktop"
'destinationFolder = "C:\path\to\folder" 'Or specific folder
If Right(destinationFolder, 1) <> "[B][COLOR=#0000ff]\[/COLOR][/B]" Then destinationFolder = destinationFolder & "[B][COLOR=#0000ff]\[/COLOR][/B]"
'Cell containing data validation in-cell dropdown
Set dataValidationCell1 = Worksheets("Lookup").Range("C2")
'Source of data validation list
Set dataValidationListSource1 = Evaluate(dataValidationCell1.Validation.Formula1)
'Create PDF for each data validation value
For Each dvValueCell1 In dataValidationListSource1
dataValidationCell1.Value = dvValueCell1.Value
Set dataValidationCell2 = Worksheets("Lookup").Range("C4")
Set dataValidationListSource2 = Evaluate(dataValidationCell2.Validation.Formula1)
For Each dvValueCell2 In dataValidationListSource2
With dataValidationCell1.Worksheet
With dataValidationCell1.Worksheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[B][COLOR=#0000ff]destinationFolder [/COLOR][/B]& "Womens" & dvValueCell1.Value & dvValueCell2.Value & ".PDF", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End With
Next
Next
End Sub