VBA to loop through a data input tab to populate contracts

[bbalch]

Hello everyone.

I wanted to see if anyone could create some VBA or suggest a better way of doing process that we are currently doing manually. An example file is saved to the Dropbox link below.

We hire temporary labor for different events and our departments submit a temporary hire spreadsheet that gathers and lists all of their hires information. This is the Data_Submitted tab in the Dropbox file.Some departments will hire up to 100 people per event. For each temporary hire we are required to fill out a contract, eg “Template” tab in the dropbox link. This is currently a manually process that I’m hoping to automate.

Ideally I would like to loop through the Data_Submitted tab and populate the template tab with each row’s data and create a new tab with that information. For example of the Data_Submitted tab row 2 is John Smith, so a contract for John Smith would be created with his information, then the data in row 3 would populate a new contract….to the last row of data.

Things I’m looking to accomplish:
1. Create a contract for each row of data on the Data_Submitted tab by populating fields on the Template tab
a. Populate Name
b. Populate Address
c. Populate Title
d. Populate City
e. Populate State / Zip
f. Populate Phone
g. Populate email
2. Create a separate tab for each new contract
3. Rename the tab to the contents of Cell G2 (LastName_FirstName_Event)
4. Save all tabs with the tabs name (G2) above as PDF

The number of rows on the Data_Submitted tab will vary based on each departments needs and will range from 3 – 100 rows.


Any suggestions on a way to improve this or VBA to accomplish this are greatly appreciated.

https://www.dropbox.com/sh/x240qlvgj2hd8f1/AABk_s_DTsI22t3zPS1m7L00a?dl=0

Thanks,
Ben

 

[Macropod]

Have you considered using mailmerge, via Word?

 

[bbalch]

Mail merge did the trick. Thank you very much!