Looping through dropdown menu to generate pdf

I have set up a file so that the table updates based on the value in the dropdown menu. The drop down menu consists of numbers stored as text (employee ID numbers starting in 0. Hence saved it as a string so that the zeros are not dropped).

I'm trying to create a code that loops through each value in the drop down menu and generate a pdf for each person, saving the pdf using the person's last name. So far, I've tweaked this code below. The main problem that I noticed is, this code seems to convert the Employee ID numbers into an actual number. (i.e. 000123 becomes 123), leaving me with a "#N/A" where the employee's name should be.


When I run this code, I get the following error: "Run-time error '13." Would appreciate any help troubleshooting this. I'm so close, yet so far.

B12 - references the drop down list
S4 contains the name of the person that updates based on what value is selected in "B12"

Please Login or Register  to view this content.

Perhaps:

Please Login or Register  to view this content.

dvCell is defined as a Range object, during your loop you are trying to assign a cell value to it, which is likely the cause of the Error Type 13 (Type Mismatch).

I'm not sure why you're assigning anything to dvCell in the loop, you don't reference it again. I suspect you can just delete that line.

I also don't believe you can declare a new variable inside a loop, so the line Dim SaveAsStr As String should be moved outside the loop, ideally at the top with the other declarations.

This code will work as such,

Please Login or Register  to view this content.

but for this specific problem

this code seems to convert the Employee ID numbers into an actual number. (i.e. 000123 becomes 123), leaving me with a "#N/A" where the employee's name should be.

it maybe best to post an example file to address the problem.

Originally Posted by bakerman2

This code will work as such,

Please Login or Register  to view this content.

but for this specific problem


it maybe best to post an example file to address the problem.

Thanks, Bakerman. I ran the code you posted, and it worked for one iteration, producing two reports. But when I tried running it again, I got a "Run-time error" (image posted)


Here's a link to the file. The script concerns the worksheet at the end called "Table." Basically, I'm trying to generate a report formatted in this worksheet. The dropdown list is in B12, and contains the unique identifiers of each manager we want to generate a report for. Would appreciate any help to figure out how to get the loop to work so that I can generate hundreds of pdfs with a click of a button.

My colleague and I've gotten the macro to either generates pdfs that don't contain the updated data or produce one report that doesn't loop.

Figured it out! It was simply a matter of removing one line of code, and now it works!

Thanks for the input!

Please Login or Register  to view this content.