SOLVED

How do I automate and populate multiple separate forms from excel data?

Copper Contributor

Help :

 

I am looking for forms to be created from excel data for 190+ employees for their annual performance review. Previously this has always been a manual process for HR to create all the forms. I have two separate form templates and all the data I need populated on the forms in a spreadsheet (emp name, dept, title, etc.). The spreadsheet data also has a column noting which form they should receive. Is there a quick way to automatically create all the forms from the data or do I need to manually enter? See attached spreadsheet for example data and the two separate forms (standard vs supervisor forms). Any help is so so greatly appreciated. 

6 Replies

@jhuskey 

See the attached version. It is now a macro-enabled workbook so you'll have to allow macros when you open it.

Wow.. how did you do that? I'm not sure I understand macros.
THANK YOU
best response confirmed by allyreckerman (Microsoft)
Solution

@jhuskey 

You can view the code as follows:

Press Alt+F8.

Select GenerateForms.

Click Edit.

 

The code determines the last used row in column B.

Then it loops through the rows from row 2 to the last row.

Depending on the type of review, it copies the Standard Review sheet or the Supervisory Review sheet to a new workbook.

It copies the contents of the cells in columns B, D, E and F to the appropriate cells in the review.

Finally it saves the new workbook with the name of the employee in the same folder as the template workbook.

@HansVogelaar 

 

Hi Hans,

Are you able to alter the code to save the file to PDF rather than XLS?

I tried altering the below section of code with no luck.

 

wTrg.SaveAs Filename:=wSrc.Path & "\" & sSrc.Range("B" & r).Value & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook

 

@Accountant85 

Use

 

wTrg.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wSrc.Path & "\" & sSrc.Range("B" & r).Value & ".pdf"

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@jhuskey 

You can view the code as follows:

Press Alt+F8.

Select GenerateForms.

Click Edit.

 

The code determines the last used row in column B.

Then it loops through the rows from row 2 to the last row.

Depending on the type of review, it copies the Standard Review sheet or the Supervisory Review sheet to a new workbook.

It copies the contents of the cells in columns B, D, E and F to the appropriate cells in the review.

Finally it saves the new workbook with the name of the employee in the same folder as the template workbook.

View solution in original post