Save PDF's from a dropdown menu using VBA

[ob1tech77]

Hello! Thank you for taking the time to read my question. I'm attaching two pictures for reference. We use the dropdown menu to pull information from another sheet. We currently select a student from the dropdown menu, then we print to pdf manually. At times we have 110 students and spend a lot of time doing this one by one. I need help to figure out a way to print whatever is on that dropdown list automatically. If possible the option to save every file with the name of the student Cell B5, and grading period cell C4 with the year at the end (Example Obed Gaytan 1-4th 9wks-Speech 23-24.pdf). Even better if we have a way to save every sheet individually and also with an option to save all sheets in one .pdf file (Example: All Sheets-4th 9wks-Speech-23-24.pdf) The template extends from Cell A2:J55. Any help will be greatly appreciated.

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could upload a copy of your file including any macros you are currently using, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[ob1tech77]

It is hard to work with pictures. It would be easier to help if you could upload a copy of your file including any macros you are currently using, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thanks for the reply. I was able to figure it out by using this macros. My next task is to be able to save all pdfs into one file and then maybe an option to print. It would be greatly appreciated if you have any suggestions.

Public Sub Create_PDF1s()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim SpecialSymbol As Variant
    Dim destPath As String
    Set Sfolder = Application.FileDialog(msoFileDialogFolderPicker) 'open select folder windows
    Sfolder.Title = "Select destination folder"
    If Sfolder.Show <> -1 Then Exit Sub
    destPath = Sfolder.SelectedItems(1)
    SpecialSymbol = Array("\", "/", ":", "*", "?", """", "<", ">", "|") 'special symbol that can not in the file name
      
    'Cell containing data validation in-cell dropdown
  
    Set dataValidationCell = Worksheets("StudentLOG").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.Range("A2:J55")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
      
End Sub

 

[eiloken]

Thanks for the reply. I was able to figure it out by using this macros. My next task is to be able to save all pdfs into one file and then maybe an option to print. It would be greatly appreciated if you have any suggestions.

Public Sub Create_PDF1s()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim SpecialSymbol As Variant
    Dim destPath As String
    Set Sfolder = Application.FileDialog(msoFileDialogFolderPicker) 'open select folder windows
    Sfolder.Title = "Select destination folder"
    If Sfolder.Show <> -1 Then Exit Sub
    destPath = Sfolder.SelectedItems(1)
    SpecialSymbol = Array("\", "/", ":", "*", "?", """", "<", ">", "|") 'special symbol that can not in the file name
     
    'Cell containing data validation in-cell dropdown
 
    Set dataValidationCell = Worksheets("StudentLOG").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.Range("A2:J55")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
     
End Sub

it been a while, i dont test it yet, but try this:

Public Sub Create_PDF1s()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim SpecialSymbol As Variant
    Dim destPath As String
    Dim wb As Workbook 'new by me
    Set Sfolder = Application.FileDialog(msoFileDialogFolderPicker)
    Sfolder.Title = "Select destination folder"
    If Sfolder.Show <> -1 Then Exit Sub
    destPath = Sfolder.SelectedItems(1)
    SpecialSymbol = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    Set dataValidationCell = Worksheets("StudentLOG").Range("B1")
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
    If WorksheetFunction.CountA(dataValidationListSource) = 0 Then Exit Sub 'check if source is empty
    Workbooks.Add
    Set wb = ActiveWorkbook 'create new workbook
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With dataValidationCell.Worksheet
            .PageSetup.PrintArea = .Range("A2:J55").Address 'set print area
            .Copy after:=wb.Sheets(wb.Sheets.Count) 'copy to new wb
        End With
    Next
    wb.Sheets(1).Delete 'delete blank sheet
    wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    wb.Close (False)
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[ob1tech77]

it been a while, i dont test it yet, but try this:

Public Sub Create_PDF1s()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    Dim SpecialSymbol As Variant
    Dim destPath As String
    Dim wb As Workbook 'new by me
    Set Sfolder = Application.FileDialog(msoFileDialogFolderPicker)
    Sfolder.Title = "Select destination folder"
    If Sfolder.Show <> -1 Then Exit Sub
    destPath = Sfolder.SelectedItems(1)
    SpecialSymbol = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    Set dataValidationCell = Worksheets("StudentLOG").Range("B1")
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
    If WorksheetFunction.CountA(dataValidationListSource) = 0 Then Exit Sub 'check if source is empty
    Workbooks.Add
    Set wb = ActiveWorkbook 'create new workbook
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
        With dataValidationCell.Worksheet
            .PageSetup.PrintArea = .Range("A2:J55").Address 'set print area
            .Copy after:=wb.Sheets(wb.Sheets.Count) 'copy to new wb
        End With
    Next
    wb.Sheets(1).Delete 'delete blank sheet
    wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    wb.Close (False)
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Here are my test results.
It was able to copy everything over to a temporary file, but then it throws this error: Run-time error '91': Object variable or with block variable not set.

When I debug, this where the error is:

wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

 

[eiloken]

Here are my test results.
It was able to copy everything over to a temporary file, but then it throws this error: Run-time error '91': Object variable or with block variable not set.

When I debug, this where the error is:

wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

you should change your exported file name: "destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", may be like: ""destPath & "\Something-1stGP-Speech-23-24.pdf""

 

[ob1tech77]

you should change your exported file name: "destPath & "\" & dvValueCell.Value & "-1stGP-Speech-23-24.pdf", may be like: ""destPath & "\Something-1stGP-Speech-23-24.pdf""

Awesome that worked! I really appreciate your help. One last question. I selected data validation range for cells A2:A126. Currently there is only data on cells A2 through A6. The rest are empty, but it's still saving 125 pdfs in one file. I noticed this line in the code which I suppose checks for 0 and deletes empty sheets. Is there something else that I can do so it will stop at 4 instead of always printing 125 pdfs.

If WorksheetFunction.CountA(dataValidationListSource) = 0 Then Exit Sub 'check if source is empty

 

[BigDawg15]

Try:

If WorksheetFunction.CountA(dataValidationListSource) > 4 Then Exit Sub 'check if source is empty

 

[ob1tech77]

Try:

If WorksheetFunction.CountA(dataValidationListSource) > 4 Then Exit Sub 'check if source is empty

Unfortunately, that didn't work. It still saved 125 pages.

 

[BigDawg15]

Try changing the Data Validation to A2:A6 instead of A2:A126.