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.

 

[ob1tech77]

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

A copy of this sheet is going to be used by different people in different locations. One location might have 125 students and another location might have 50 students. It depends on the location. For testing purposed I only have four entries in the data validation and ignoring blanks, but it's saving 125 either way. The initial code used, which saves individual pdfs per validation entry, stops at 4 and only saves for 4 pdfs.

 

[eiloken]

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

You can create table with name for data validation source, so each time you delete or add data, table source will be auto resize and your drop down list will resize with it

 

[ob1tech77]

You can create table with name for data validation source, so each time you delete or add data, table source will be auto resize and your drop down list will resize with

I don't have data in a table. I used this formula for the data validation source: =OFFSET(Caseload!$B$2,,,COUNTA(Caseload!$B:$B)-1) It did that trick, and it updates the validation list when I add to B column. It also fixes the issues of saving blank pdfs. Thank you so much for your help.