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

%3CLINGO-SUB%20id%3D%22lingo-sub-2823613%22%20slang%3D%22en-US%22%3EHow%20do%20I%20automate%20and%20populate%20multiple%20separate%20forms%20from%20excel%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2823613%22%20slang%3D%22en-US%22%3E%3CP%3EHelp%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20forms%20to%20be%20created%20from%20excel%20data%20for%20190%2B%20employees%20for%20their%20annual%20performance%20review.%20Previously%20this%20has%20always%20been%20a%20manual%20process%20for%20HR%20to%20create%20all%20the%20forms.%20I%20have%20two%20separate%20form%20templates%20and%20all%20the%20data%20I%20need%20populated%20on%20the%20forms%20in%20a%20spreadsheet%20(emp%20name%2C%20dept%2C%20title%2C%20etc.).%20The%20spreadsheet%20data%20also%20has%20a%20column%20noting%20which%20form%20they%20should%20receive.%20Is%20there%20a%20quick%20way%20to%20automatically%20create%20all%20the%20forms%20from%20the%20data%20or%20do%20I%20need%20to%20manually%20enter%3F%20See%20attached%20spreadsheet%20for%20example%20data%20and%20the%20two%20separate%20forms%20(standard%20vs%20supervisor%20forms).%20Any%20help%20is%20so%20so%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2823613%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2823912%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20automate%20and%20populate%20multiple%20separate%20forms%20from%20excel%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2823912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1178805%22%20target%3D%22_blank%22%3E%40jhuskey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20It%20is%20now%20a%20macro-enabled%20workbook%20so%20you'll%20have%20to%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2824144%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20automate%20and%20populate%20multiple%20separate%20forms%20from%20excel%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2824144%22%20slang%3D%22en-US%22%3EWow..%20how%20did%20you%20do%20that%3F%20I'm%20not%20sure%20I%20understand%20macros.%3CBR%20%2F%3ETHANK%20YOU%3C%2FLINGO-BODY%3E
New 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. 

4 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

@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.