Copy Excel Data Entry Values to Another Worksheet with VBA
Question details
The user needs a VBA macro in WPS Spreadsheets that copies calculated data entry values from an entry form to the next empty row on another worksheet, then clears only manual input cells while preserving formulas.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a data entry workflow that transfers results from a form to a storage worksheet.
- Observed behavior
- No working macro is currently available; recorded macros were deleted, and the user needs a reliable generic VBA example.
Open the workbook in WPS Spreadsheets, save it as a macro-enabled workbook (.xlsm), and identify the entry worksheet, storage worksheet, input cell addresses, destination columns, and the column used to find the next empty row.
Create a VBA Macro That Copies Entry Values to the Next Empty Row and Clears Only Input Cells
Use this method when you want WPS Spreadsheets to transfer data from an entry form to a storage worksheet and preserve formulas between entries.
The macro works by mapping each entry cell to a destination column, assigning the source cell's Value to the destination row, finding the next empty row, and clearing only the cells intended for manual input.
Open the workbook in WPS Spreadsheets and press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer pane, right-click the VBAProject for the current workbook, choose Insert > Module, and open the new module window.
At the top of the module, set the source and destination worksheets, for example: Set entry = ThisWorkbook.Worksheets("Entry") and Set storage = ThisWorkbook.Worksheets("Storage").
Find the next empty row with nextRow = storage.Cells(storage.Rows.Count, 1).End(xlUp).Row + 1, then assign each source cell's .Value to the matching destination column, such as storage.Cells(nextRow, 1).Value = entry.Range("C5").Value.
After the transfer, clear only the input cells with entry.Range("C5,C7,D10").ClearContents. Do not clear cells that contain formulas required for the next entry.
Test the Macro in Debug Mode Before Running It on Live Data
Use this to verify the destination row and mapped columns without damaging existing data.
Copy Data Entry Values to Another Worksheet with WPS Spreadsheets VBA
Use WPS Spreadsheets to automate the transfer from an entry form to a storage worksheet. The macro copies calculated values, finds the next empty row, clears only manual input cells, and keeps formulas intact.
- 1. Open the workbook: Open the target workbook in WPS Spreadsheets and enable macros when prompted.
- 2. Open the VBA editor: Press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
- 3. Add the transfer macro: In Project Explorer, right-click the current VBAProject, choose Insert > Module, and create a macro that maps each entry cell to its destination column using .Value assignment.
- 4. Find the next empty row: Inside the Storage worksheet, use storage.Cells(storage.Rows.Count, 1).End(xlUp).Row + 1 to set the destination row.
- 5. Clear only input cells and run: After copying, clear only the manual input ranges with .ClearContents, then run the macro with F5 or assign it to a button.
Frequently Asked Questions
Can the VBA macro copy calculated results instead of formulas?
Yes. Assign the source cell's .Value property to the destination cell instead of .Formula or .FormulaR1C1, and the displayed calculated result is copied.
How do I clear input cells without deleting formulas?
Clear only the specific manual input ranges, for example with Range("C5,C7,D10").ClearContents, and exclude any cells that contain formulas.
How does the macro find the next empty row in the storage worksheet?
Use a column that is always filled after each transfer, then calculate nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 in the storage worksheet.
Will this macro work in WPS Spreadsheets?
Yes, WPS Spreadsheets supports VBA macros. Open the editor with Alt+F11 and save the workbook as a macro-enabled .xlsm file if required.
What should I do if the macro does not run?
Enable macros in Security or Trust Center settings and check that the worksheet names and cell references in the code match your workbook.
