VBA & Macro Problems

WPS Spreadsheet: Automatically Add a Complete Row to Another Worksheet

Phi Hung Vo Nov 12, 2026 868 views

Question details

The user needs a new project added from one worksheet to another as a complete row, including blank monthly columns.

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: A project list is linked between two worksheets, and each new project should create a physical row in a destination table while preserving its monthly columns.
Observed behavior: The current link or formula returns only the project name in a destination cell. It does not insert a physical row or maintain two independently structured tables.

Before you start

Confirm whether both worksheets are in the same workbook and decide whether deletions must also synchronize. Name the source and destination ranges as Excel-style tables, and identify the project column and monthly columns before creating automation.

Solution 1Recommended

Use a worksheet-change macro to add a complete destination row

Use this method when a new project must create a physical row in another worksheet and leave its monthly cells blank.

A formula can display a project name, but it cannot reliably insert physical rows into a separate table. A worksheet event macro can detect a new project, add a row to the destination table, copy the project name, and preserve blank monthly columns.

The exact macro depends on the workbook structure, including table names, worksheet names, duplicate handling, and whether deletions should be synchronized.

Create two structured tables

On the source worksheet, select the project list and choose Insert > Table. Repeat on the destination worksheet, including the project column and all monthly columns. Give the tables clear names such as tblProjects and tblMonthly.

Open the worksheet code window

Press Alt+F11 to open the VBA editor, expand the workbook in Project Explorer, and double-click the source worksheet that contains the project list.

Add the change event logic

Insert a Worksheet_Change event that checks the source Project column, searches the destination table for the project name, and uses the destination table's ListRows.Add method when the name is not found. Write the project name into the first destination column and do not write values to the monthly columns.

Save and test the automation

Save the workbook in a macro-enabled format supported by your WPS installation, close the VBA editor, and enter a new project in the source table. Verify that a complete destination row appears with blank monthly cells and that an existing project does not create a duplicate.

Use a worksheet-change macro to add a complete destination row

Macro design depends on the workbook: A sample workbook is required to produce reliable synchronization code for additions, deletions, renamed projects, duplicate names, and table-specific column layouts.

Solution 2

Generate a destination project view with Power Query

Use this option when the destination is a refreshed report or view and does not need independently inserted physical rows.

Solution 3

Use a dynamic array for a display-only project list

Use this lightweight option when the destination only needs to display project names and calculated blank monthly fields.

Automate Project Row Synchronization in WPS Spreadsheet

WPS Spreadsheet can organize the source and destination data as tables and use VBA-compatible worksheet event logic to add a complete destination row when a new project is entered. It also supports formula-based and refreshable alternatives when physical row insertion is not required.

1. Create the source and destination tables: In WPS Spreadsheet, select each range and choose Insert > Table. Include the project column and all monthly columns in the destination table.
2. Open the macro editor: Press Alt+F11 or open the Developer tab and select Visual Basic, then open the source worksheet's code window.
3. Add row-synchronization logic: Configure a Worksheet_Change event to detect a new project, search the destination table, add a row when needed, and write only the project name.
4. Test and save the workbook: Enter a test project, verify the complete row and blank monthly cells, then save in a macro-enabled format supported by your WPS Office version.

Automatically add a destination table row for each new projectKeep monthly columns available as blank cells for later entryUse familiar table, Data, and VBA workflow controlsOpen and edit Microsoft Excel XLSX and macro-enabled XLSM files

microsoft office alternative - wps office

Frequently Asked Questions

Can a formula insert a complete row in another WPS Spreadsheet worksheet?

No. A formula can return or spill project names into cells, but it cannot reliably insert a physical row into another structured table. Use a worksheet-change macro for physical row insertion.

How do I keep the monthly columns blank when a new project is added?

Add a new row to the destination table and write only the project name into its project column. Do not copy values into the monthly columns; table formatting can still extend automatically.

What information is needed to write the exact synchronization macro?

The workbook structure is needed, including worksheet names, source and destination table names, project-column names, monthly-column layout, duplicate rules, and whether deletions or renamed projects must synchronize.

Should I use Power Query instead of VBA?

Use Power Query when the destination is a refreshed report or view. Use VBA when users must see a physical destination row added immediately and continue editing its monthly cells independently.

Can WPS Spreadsheet open an Excel macro-enabled workbook?

WPS Spreadsheet can open many XLSX and XLSM files, but VBA behavior depends on the WPS Office version and the macro's compatibility. Test the event macro in a copy of the workbook and confirm that macros are enabled.

Excel & Data Problems