Use VBA to Add Team Members and Update Excel Totals
Question details
The user needs to automate the process of adding new team member rows to a tracking sheet while preserving formatting, copying formulas, and updating total calculations automatically using VBA.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing team rosters or project resource sheets where new members are frequently added, requiring repetitive manual updates to formulas and totals.
- Observed behavior
- Currently, adding a row requires manual copying of formatting and formulas, and manually adjusting the range for total and utilization calculation formulas.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have a template row set up with the correct formulas and formatting ready to be copied by the macro.
Use a VBA Macro with Dynamic Range References
This method uses a VBA script to dynamically find the last row of your team list, insert a new row, copy a template row's formatting and formulas, and update the totals automatically.
Using dynamic range references (like finding the last used row) ensures that your macro will always insert the new row in the correct place, regardless of how many team members you currently have.
By copying an existing template row, you carry over both the visual formatting and complex calculations. You can then use VBA to clear just the input cells so the new row is ready for fresh data.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click Insert from the top menu, then select Module to create a blank script window.
Write VBA code that defines the last row using Cells(Rows.Count, 1).End(xlUp).Row. Use the EntireRow.Insert method to add a line, and Range.Copy to duplicate the formatting and formulas from your template row to the newly inserted row.
Add lines to your code using Range.ClearContents to clear only the manual input cells (like names or base hours) in the new row. Be sure to reference only the specific columns containing hardcoded data so you leave the formula columns intact.
Close the VBA editor and return to your worksheet. Run the macro by pressing Alt + F8, selecting your newly created macro from the list, and clicking Run.

Automate Your Spreadsheets with VBA in WPS Office
WPS Spreadsheet offers robust support for VBA macros, allowing you to seamlessly automate repetitive tasks like inserting team member rows and updating formulas. It provides a lightweight, highly compatible environment for your macro-enabled workbooks.
- 1. Enable the Developer Tab: Open WPS Spreadsheet, go to Options, and enable the 'Developer' tab to access all macro tools.
- 2. Open the VBA Editor: Click the 'Developer' tab on the ribbon and select 'Visual Basic' or simply press Alt + F11.
- 3. Write and Run Macros: Paste your team-adding VBA script into a new Module and run it exactly as you would in Excel.

Frequently Asked Questions
Why doesn't my total formula update when the macro inserts a new row?
If you insert a row exactly at the bottom of a SUM range (e.g., right above the total row), Excel might not automatically expand the range. To fix this, your VBA code should either redefine the SUM formula for the total cell, or you should program the macro to insert the new row inside the existing range rather than at the very edge.
How can I clear specific cells in the new row without deleting formulas?
In your VBA code, explicitly reference the specific columns you want to clear in the newly inserted row using the ClearContents method. For example: Cells(newRow, 1).ClearContents clears column A while leaving formula columns untouched.
Do I need to save my workbook in a special format after adding this macro?
Yes, any workbook containing VBA macros must be saved as a Macro-Enabled Workbook. Choose File > Save As and select .xlsm from the file format dropdown to ensure your VBA code is preserved.
Can I assign this VBA macro to a button on the worksheet?
Yes. Go to the Developer tab (or Insert tab), select Shapes to draw a button, right-click the shape, and choose 'Assign Macro'. Select your newly created macro from the list to run it easily with a single click.




