VBA & Macro Problems

How to Automatically Generate Invoice Pages in WPS Spreadsheets from an Excel Template

Nimra Malik Nov 13, 2026 868 views

Question details

The user needs to stop manually creating, completing, saving, and closing a separate sheet for every invoice and instead generate invoice pages automatically from an existing Excel invoice template.

How to Automatically Generate Invoice Pages in WPS Spreadsheets from an Excel Template

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user maintains an invoice workbook and wants each new invoice record to produce an invoice page automatically.
Observed behavior: Currently every invoice requires a separate page to be created, completed, saved, and closed manually, which is repetitive and inefficient.

Before you start

Make sure your invoice records are stored in an Excel Table with consistent column headers, and create a clean invoice template sheet before running any automation. If you plan to use VBA, enable macros in WPS Spreadsheets and save the workbook as a macro-enabled file.

Solution 1Recommended

Use a VBA Macro to Generate Invoice Pages from an Excel Table

Best for invoice workbooks with many records: one macro copies the invoice template, fills in each record, and generates a page automatically.

This method separates raw invoice data from the invoice layout. The macro loops through the Excel Table and uses the template as the single source for page formatting, so changes to the template apply to all generated invoices.

Store invoice records in an Excel Table

In WPS Spreadsheets, select the invoice data range, click Insert > Table, and check My table has headers if your first row contains column names.

Create a clean invoice template sheet

Add a new sheet named Invoice Template and lay out the invoice fields exactly as you want each generated page to appear.

Open the VBA editor and insert a module

Go to Developer > Visual Basic or press Alt+F11, click Insert > Module, and paste a macro that loops through each table row, copies the template, and fills the invoice number, customer, and line-item fields.

Run the macro

Press F5 or go to Developer > Macros, select the macro, and click Run to generate each invoice page.

Use a VBA Macro to Generate Invoice Pages from an Excel Table

Keep macros enabled: If WPS or Excel blocks the macro, enable macros for the workbook and save it as a .xlsm file so the automation remains available.

Solution 2

Use a Dropdown Invoice Number and XLOOKUP Formulas in One Template

A formula-only alternative: select an invoice number from a dropdown and let XLOOKUP retrieve the record into a single invoice layout.

Solution 3

Generate Many PDF Invoices with WPS Writer Mail Merge

Use this when you need batch PDF or printed invoices from an Excel invoice table without writing VBA.

Automate invoice pages in WPS Spreadsheets

Generate Invoice Pages Automatically in WPS Spreadsheets

Use an Excel Table plus a WPS Spreadsheets VBA macro to copy an invoice template and populate a page for every invoice record, or use a dropdown with XLOOKUP for a simpler one-page workflow. WPS Office handles .xlsx and .xlsm files, so existing Excel templates and macros can be reused.

1. Convert invoice records to an Excel Table: In WPS Spreadsheets, select the invoice data and click Insert > Table to create the structured source the macro will read.
2. Create the invoice template sheet: Add a sheet named Invoice Template and design the layout once, leaving cells blank for invoice number, customer, and line items.
3. Insert and run the VBA macro: Open Developer > Visual Basic, insert a module, paste the loop macro, then press F5 to generate invoice pages automatically.
4. Save or export the generated pages: After the macro runs, go to File > Save As and choose PDF or print the generated sheets as needed.

Automatically creates invoice pages from one Excel Table instead of manual sheet copy and closeKeeps formatting consistent through a reusable invoice templateSupports .xlsx and .xlsm Excel formats for smooth migrationOffers a free, lightweight Office suite with familiar spreadsheet menus and VBA workflowsAlso provides formula and mail merge options for batch PDF invoices

microsoft office alternative - wps office

Frequently Asked Questions

Does this invoice automation require VBA knowledge?

No. You can use the dropdown and XLOOKUP method for a no-code solution. The VBA method is only needed if you want a one-click loop that creates many pages automatically.

Can WPS Spreadsheets run Excel invoice macros?

Yes, WPS Spreadsheets on Windows can run many Excel VBA macros. Enable macros and save the workbook in a macro-enabled format such as .xlsm before running the automation.

How can I save each generated invoice as a separate PDF?

In the VBA macro, add an export command for each copied invoice, or use WPS Writer mail merge with your Excel invoice table and finish to PDF.

What should I prepare before running the macro?

Store all invoice records in an Excel Table with unique invoice numbers and consistent column names, create a clean invoice template sheet, and enable macros if prompted.

Why use one template with XLOOKUP instead of many invoice sheets?

A single template keeps the workbook small and lets you update formulas or formatting once, but it only shows one selected invoice at a time.

Excel & Data Problems