0

I have a vba excel to pdf code but I am unsure how to loop it through a data validation list. The data validation list is a collection of player names with each selection within the list interacting with vlookup's on the excel sheet. This means that each selection will result in different data pulled onto the sheet. Is there a way to loop and print each individual data selected sheet to a PDF but have all PDF sheets in a single file? The dvCell is located in cell C8 of sheet "Gym Weekly Template".

Below is the code I currently have:

Sub PDFActiveSheet()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = Worksheets("Gym Weekly Template")
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
wsA.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=myFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
  & vbCrLf _
  & myFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub

Much appreciated if you can help!

Jackson Privett
  • 3
  • 4
  • What is in there in the data validation list? – Subodh Tiwari sktneer Jan 12 '19 at 05:57
  • https://stackoverflow.com/a/31694603/4539709 – 0m3r Jan 12 '19 at 07:56
  • The DV list is a list of player names found at 'Testing Data'!$A$6:$A$35 – Jackson Privett Jan 12 '19 at 10:46
  • @JacksonPrivett, I don't understand your question. Based on the title, you want to loop through all items in a data validation list and export all sheets to one PDF file. Are we to assume that your data validation list contains the names of sheets which need to exported? – chillin Jan 12 '19 at 19:44
  • The validation list interacts with vlookup values throughout the sheet, therefore each value selection within the data validation list will result in a different sheet – Jackson Privett Jan 13 '19 at 03:51

0 Answers0