VBA & Macro Problems

How to Automatically Populate, Print, and Save Excel Records

Maira Mehtab Sep 25, 2026 869 views

Question details

The user needs a method to map row-by-row source data into an Excel form template, automatically save or print each populated form, and loop through the entire dataset.

How to Automatically Populate, Print, and Save Excel Records

Product: Excel
Device & OS: not provided
Scenario: Generating multiple forms or documents based on rows of data stored in a separate worksheet.
Observed behavior: The user currently has source data and a form template on separate sheets but lacks an automated workflow to populate, print/save, and advance to the next row.

Before you start

Ensure your source data is organized in clean rows and columns without merged cells, and save a backup copy of your workbook before running any VBA macros.

Solution 1Recommended

Use a VBA Macro for Complete Automation

VBA allows you to loop through each row of your source data, insert the values into your form template, and automatically print or save each version.

A VBA script provides the highest level of automation, allowing you to iterate through your entire dataset with a single click.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click Insert > Module in the top menu to create a new script area.

Write the Loop Macro

Write a For loop that reads data from your source sheet row by row (e.g., For i = 2 To LastRow).

Map Data to Form Fields

Inside the loop, assign the source cell values to the specific form cells (e.g., Sheets("Form").Range("B2").Value = Sheets("Data").Cells(i, 1).Value).

Add the Print or Save Command

Add a command within the loop to execute the desired action, such as Sheets("Form").PrintOut or ExportAsFixedFormat to save as PDF.

Run the Macro

Press F5 or click the Run button on the toolbar to execute the macro and process all records.

Macro Security: Make sure to enable macros in your Excel Trust Center settings and save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

Solution 2

Map Data Using Dynamic Formulas

If you only need to view or manually print successive records, array formulas can map the data dynamically without using macros.

Solution 3

Alternative: Use Mail Merge for Document Generation

For standard document generation, linking your Excel data to a Word document via Mail Merge is often simpler than creating complex Excel macros.

Automate Form Generation with WPS Office

WPS Spreadsheet fully supports VBA macros, advanced array formulas, and offers seamless Mail Merge integration with WPS Writer, allowing you to easily automate the populating, printing, and saving of your records.

1. Download WPS Office: Download and install WPS Office Free on your device.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing data workbook.
3. Access Developer Tools: Navigate to the Developer tab to enable the VBA editor and manage your macros.
4. Execute Automation: Run your macro to populate, print, and save records, or use the Mailings tab in WPS Writer to start a Mail Merge.

Fully compatible with Microsoft Excel (.xlsx, .xlsm) formats and existing VBA scripts.Built-in advanced VBA editor to write, edit, and execute automation macros seamlessly.Integrated Mail Merge feature across WPS Spreadsheet and WPS Writer.Lightweight, fast, and free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Can I save each populated Excel form as a separate PDF automatically?

Yes, by using a VBA macro, you can use the ExportAsFixedFormat method within your loop to automatically save each populated form as an individual PDF file. You can even generate a dynamic file name based on the record data.

Why is the CHOOSEROWS formula not working in my Excel version?

The CHOOSEROWS function is a newer dynamic array function available in Microsoft 365. If you are using an older version of Excel, you can achieve similar results using INDEX and MATCH formulas, or standard VLOOKUP functions linked to an index cell.

Is Mail Merge better than VBA for generating forms?

Mail Merge is generally easier and faster to set up for standard text-based forms, letters, or labels. However, using VBA directly in Excel is better suited when you need complex calculations, conditional formatting, or if you need to retain specific Excel grid layouts in your final output.

Excel & Data Problems