Automated printing for 150 sets of data

[ElvinK]

I have set of data in sheet 1. There are two other sheets (let's name it page1 and page2) at which I use it as the template for a form which I going to print.

At the top of page1 and page2, there is a drop-down (using data validation). The drop-down shows the name of employees as entred in sheet 1. The rest of the content on page1 and page2 uses the VLOOKUP function based on the data on sheet 1.

There is a total of 150 sets of data (150 employees) involved.I would like to automate the process of selecting the employee's name and saving page1 and page2 in PDF format.

How can I use Macro to help me. Urgent help appreciated.

[Richard Buttrey]

Uploading the workbook and knowing how familiar you are with VBA would be a good start.

It's a relatively simple task and you just need to use a loop to work down the list of employee names and save the file each time.

You don't say whether pages 1 & 2 should be in two pdf files or both together in one.

What do you do once you've created the file. I guess you're forwarding them to each employee.
Have you considered sending them an email that has the page 1 & 2 date embedded in it rather than creating 150 files? You could of course attach a pdf file to each email.

[ElvinK]

Hey there,

Thanks for replying.

Have zero knowledge about VBA to start with. Only knew about VBA after searching up the web.
Due to some confidentiality issue and policy, I am unable to upload the original file here.

Attached is a replica of the original document (with lesser data and information of course).

To address the part of forwarding it to the employees, currently we would like to save it as a PDF first and when the time comes, we will then send it to the employees.

[Richard Buttrey]

Is this an EXACT replica? We often find that when we give an answer to what is a trivial non representative example, when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

If not please upload the actual workbook. You can delete most of the records but leave a sample.

Exactly what should the pdf contain?

You previously mentioned pdfs for both page 1 & page 2 but since page 2 is simply a repeat of page 1 albeit it laid out differently isn't just page 2 sufficient?

If not please mock up in Excel EXACTLY what a pdf file or files should look like.

Finally what should the files be called. I'm assuming the employee name should be in the file name but is there anything else?

[ElvinK]

I've updated the thread with the exact replica of the workbook.

My bad. To clarify, there are 3 sheets. Data, sheet 1 and sheet 2. sheet 1 and sheet 2 is to be exported/printed to PDF using a scale of 120%.

PDF to be named as employee name underscore 2019 (E.g Gary_2019)