VBA & Macro Problems

How to Automatically Transfer Excel Form Records with a Macro

Rana Garcia Sep 28, 2026 869 views

Question details

The user needs a macro to transfer submitted form data to a master data sheet as separate records without formulas changing or overwriting existing entries.

Automatically Transfer Excel Form Records with a Macro

Product: Excel
Device & OS: not provided
Scenario: Creating an automated form entry system where each form submission is logged as a distinct row in a data sheet.
Observed behavior: The current macro copies values to a temporary sheet, but capturing new records causes formulas to change or existing data to be overwritten instead of appending to a new line.

Before you start

Before editing any VBA macros, save a backup copy of your workbook. Ensure your file is saved as an Excel Macro-Enabled Workbook (.xlsm) so your code is retained after closing.

Solution 1Recommended

Append Data to the Next Empty Row using VBA

Modify your macro to find the last used row in your data sheet and paste the new form values into the subsequent empty row as plain values.

The issue with formulas changing or data being overwritten is typically caused by referencing static cells or copying formulas instead of raw values. By using a VBA script that calculates the next empty row, you ensure each submission creates a brand new record without disrupting previous entries.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Right-click your project in the Project Explorer panel on the left, select 'Insert', and then click 'Module'.

Write the Row Calculation Code

Create a macro that calculates the first empty row in your destination sheet. Use a variable like: erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1.

Transfer Values Directly

Assign the values from your form fields directly to the corresponding columns in the empty row to prevent formula shifting. For example: Sheet2.Cells(erow, 1).Value = Sheet1.Range("B2").Value.

Clear Form Fields (Optional)

Add lines to clear the original form fields after the data is transferred, preparing the sheet for the next entry (e.g., Sheet1.Range("B2").ClearContents).

Assign Macro to a Button

Return to your Excel sheet, insert a shape or button from the Developer tab, right-click it, and select 'Assign Macro' to link your new script.

Append Data to the Next Empty Row using VBA

Value Transfer: By transferring only the .Value instead of copying the whole cell, you eliminate the risk of shifting formulas or carrying over unwanted formatting.

Solution 2

Use the Built-in Data Form Feature (No VBA)

If you want to avoid writing macros, use the built-in Excel Data Form tool to quickly enter records directly into a formatted table.

Automate Workflows with WPS Office

Create and Run Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for VBA macros, allowing you to automate data entry forms and manage large datasets efficiently. It offers a familiar interface, making it easy to create, edit, and run VBA scripts to automatically transfer form records.

1. Open WPS Spreadsheet: Launch WPS Spreadsheet and navigate to the 'Developer' tab on the top ribbon.
2. Access the VBA Editor: Click on 'Visual Basic' to open the familiar VBA editor environment.
3. Automate Your Forms: Paste or write your macro to calculate empty rows and seamlessly log new form data as separate records.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats.Built-in VBA editor to automate repetitive form entries and data transfers.Lightweight software with fast processing for heavy datasets.Free to use with a highly intuitive, tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my macro overwriting the same row every time I submit the form?

This happens when the macro uses a hardcoded row reference (like Row 2) instead of dynamically calculating the next available empty row. You must use a dynamic VBA method like End(xlUp) to find the bottom of your dataset.

How do I stop my formulas from changing when copying form data?

Instead of using the .Copy method, which brings over formulas and relative references, use VBA to transfer only the static value. Set the destination cell's value equal to the source cell's value (e.g., Range("A2").Value = Range("B2").Value).

Can I run Excel macros in WPS Office?

Yes, WPS Office Spreadsheet supports VBA macros. You can open your existing .xlsm files, access the Visual Basic editor, and run your automated data transfer scripts smoothly.

Excel & Data Problems