Automate switching dropdown values and exporting to PDF

nlarski

New Member
Joined
May 11, 2017
Messages
17
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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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

New Member
Joined
May 11, 2017
Messages
17
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 :cool:

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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
I don't know your data so I am guessing on this.

Move the 2nd Set to after:
Code:
dataValidationCell1.Value = dvValueCell1.Value
Then do your 2nd For Each loop after that. The 2nd Next will go just before the 1st Next.
 
Last edited:

nlarski

New Member
Joined
May 11, 2017
Messages
17

ADVERTISEMENT

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:

Code:
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>
 
Last edited:

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
You can use ChDir() before loop but I prefer more literal. e.g.
Code:
Filename:=ThisWorkbook.Path & "\Womens" & dvValueCell1.Value & dvValueCell2.Value & ".PDF"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
check the changes in blue

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

Watch MrExcel Video

Forum statistics

Threads
1,109,428
Messages
5,528,698
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top