VBA & Macro Problems

How to Copy Excel Form Data to a Database Worksheet Using VBA

Rana Garcia Oct 15, 2026 869 views

Question details

The user needs to transfer data entered in an Excel form to a continuous database sheet using a clickable submit button.

How to Copy Submitted Excel Form Data to a Database Worksheet

Product: Excel
Device & OS: not provided
Scenario: Managing data entry workflows where input values, such as calibration data, must be saved sequentially to a separate database sheet without overwriting previous entries.
Observed behavior: The user requires a VBA macro solution because standard worksheet formulas cannot execute an on-demand copy-and-paste to the next empty row.

Before you start

Ensure you save a backup copy of your workbook before running new macros, and verify that your Excel settings are configured to enable macros.

Solution 1Recommended

Use a VBA Macro with a Form Control Button

Write a VBA script to transpose and copy vertical form data to horizontal database rows, then assign it to a clickable button.

This method uses a short VBA script to locate the next available empty row in your destination sheet. It then transfers data from specific input cells to that row with a single click.

Open the VBA Editor

Press Alt + F11 to open the VBA Editor, go to Insert in the top menu, and select Module to create a new blank script window.

Insert the Macro Code

Paste the following code into the module window: Sub SubmitData() Dim wsI As Worksheet, wsD As Worksheet, r As Long Set wsI = Worksheets("Entry") Set wsD = Worksheets("Database") r = wsD.Cells(wsD.Rows.Count, "A").End(xlUp).Row + 1 wsD.Cells(r, "A").Resize(1, 7).Value = Application.Transpose(wsI.Range("B2:B8").Value) End Sub

Customize the Variables

Modify the sheet names ("Entry" and "Database") and the input range ("B2:B8") in the code to exactly match your workbook's actual layout.

Create a Submit Button

Return to your Excel worksheet, go to the Developer tab, click Insert, and select Button (Form Control). Draw the button on your sheet, and when prompted, select the 'SubmitData' macro to assign it.

Use a VBA Macro with a Form Control Button

Save as Macro-Enabled: Remember to save your file as an Excel Macro-Enabled Workbook (.xlsm) to ensure the VBA code is permanently saved and executable.

Powerful Excel Alternative

Use WPS Spreadsheet for Advanced Data Automation

WPS Spreadsheet fully supports VBA macros, allowing you to create submit buttons and automate form data transfers seamlessly. It provides a lightweight, intuitive interface that is highly compatible with complex Excel files.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the data entry form.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'VBA Editor' to manage your automation scripts.
3. Assign Macro to a Button: Use the 'Insert' option under the Developer tab to place a Form Control button and link it to your data transfer macro.

Fully supports VBA macros for custom data entry automationSeamlessly compatible with Microsoft Excel .xlsm and .xlsx formatsLightweight application that runs smoothly and efficientlyIntuitive interface making developer tools accessible for beginners

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro overwrite existing data instead of moving to the next row?

This usually happens if the macro cannot properly identify the last row. Ensure column A (or the column specified in your End(xlUp) code) has continuous data without blank cells between entries.

Can I clear the form automatically after clicking the Submit button?

Yes, you can add a line of code at the end of your macro before 'End Sub', such as `wsI.Range("B2:B8").ClearContents`, which will erase the entry fields once the data is successfully transferred.

How do I enable the Developer tab to insert a Submit button?

Go to File > Options > Customize Ribbon. Check the box for 'Developer' in the right pane, and click OK. The Developer tab will now appear on your ribbon, granting access to Form Controls.

What if my form data is horizontal instead of vertical?

If your form data is already formatted horizontally (e.g., A2:G2), remove the `Application.Transpose` function from the VBA code. You can directly set the values using `.Value = wsI.Range("A2:G2").Value`.

Excel & Data Problems