Macro based on drop-down value

[darmok_jalad]

I’m hoping someone out there has experience with this.

I have some code that exports table data to PDFs, but it does it for every row in the table, no exceptions.

How would I write another piece of code so that it only creates a new PDF for a specific row based on a data validation cell drop-down (for when I want to make a single PDF rather than a mass export)?

So say my table has 10 columns, 200 rows and my drop-down (cell V18) references names or some other specific criteria, and then the PDF is only created based on the corresponding row data that is referenced in the V18 drop-down?

Thanks all!

 

[eiloken]

i don't really understand what you want but if you need export a table with criteria base on multiple criteria, you can create macro that can loop for each criteria and autofilter that in table then export to pdf

 

[darmok_jalad]

That may be what I have to do.

So in my table, I have 200+ rows of data and the current macro will export each row into a formatted PDF.

I created a data validation dropdown in V18 and I'm wondering if there's a way to create a macro/VBA that would select only the corresponding row based on the user name (these are the values in the dropdown) I created in column B or C, whichever I put it in.

So in essence, rather than creating a PDF for each row, I'm looking to create only a single PDF based on whatever name is in the dropdown, and I'm lost on how to do this.

 

[Skyybot]

Can you supply the Macro that you are using?

 

[darmok_jalad]

This is essentially what I'm using, I've modified it some to do what I need but the important parts are the same. It's dependant on a couple other macros being correct but I'm not the best with some other things. I'm just stuck on how to change it from doing the entire table, to just whatever row corresponds to the V18 dropdown.

Sub CreatePDFForms()
Dim PDFTemplateFile, NewPDFName, SavePDFFolder, LastName As String
Dim ApptDate As Date
Dim CustRow, LastRow As Long
With Sheet1
If .Range("G18").Value = Empty Or .Range("G20").Value = Empty Then
MsgBox "Both PDF Template and Saved PDF Locations are required for macro to run"
Exit Sub
End If

LastRow = .Range("E9999").End(xlUp).Row 'Last Row
PDFTemplateFile = .Range("G18").Value 'Template File Name
SavePDFFolder = .Range("G20").Value 'Save PDF Folder
OpenURL "" & PDFTemplateFile & "", Show_Maximized
Application.Wait Now + 0.00006

For CustRow = 5 To LastRow
LastName = .Range("E" & CustRow).Value 'Last Name
ApptDate = .Range("G" & CustRow).Value 'Appt Date
Application.SendKeys "{Tab}", True
Application.SendKeys LastName, True
Application.Wait Now + 0.00001

Application.SendKeys "{Tab}", True
Application.SendKeys .Range("F" & CustRow).Value, True 'First Name
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("I" & CustRow).Value, True 'Address
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("J" & CustRow).Value, True 'City
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("K" & CustRow).Value, True 'State
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("L" & CustRow).Value, True 'Zip
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("M" & CustRow).Value, True 'Email
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True
Application.SendKeys "{Tab}", True

Application.SendKeys Format(.Range("N" & CustRow).Value, "###-###-####"), True 'Phone
Application.Wait Now + 0.00001
Application.SendKeys "{Tab}", True

Application.SendKeys "^(p)", True
Application.Wait Now + 0.00003
Application.SendKeys "{Enter}", True
Application.Wait Now + 0.00007

If Dir(SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf") <> Empty Then Kill (SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf")
Application.SendKeys "%", True
Application.Wait Now + 0.00002
Application.SendKeys SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf"
Application.Wait Now + 0.00003
Application.SendKeys "%(s)", True
Application.Wait Now + 0.00002


Next CustRow
Application.SendKeys "^(q)", True
Application.SendKeys "{numlock}%s", True

End With
End Sub

 

[Skyybot]

Here's a simple Subroutine that selects a Row based on Criteria. You can edit to suit your needs.

Sub GetRow()
Dim wb As Workbook, sht As Worksheet, rng As Range, match As Range
Set wb = ThisWorkbook: Set sht = wb.Sheets(1): Set rng = sht.Columns("B:C")
Set match = rng.Find(sht.Range("V18").Value, LookAt:=xlWhole)
If Not match Is Nothing Then
    sht.Rows(match.Row).Select
End If
End Sub