How to Append Customer Form Data to the Next Row in Excel using VBA
Question details
The user needs a way to take customer details entered in separate vertical input cells and automatically append them horizontally to the next available row in an existing list or table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up an automated data entry form where inputs are filled vertically and then migrated into a structured horizontal database list.
- Observed behavior
- Instead of manual copy-pasting and transposing, the goal is to automatically log the form's data into the next blank row in distinct columns.
Before running any VBA macros, ensure your workbook is saved in a macro-enabled format (.xlsm) and that the Developer tab is visible on your ribbon so you can access the Visual Basic Editor.
Use a VBA Macro to Automate Appending Data
Write a VBA script to dynamically find the last used row in your table and transpose your vertical form inputs horizontally into it.
This method uses the End(xlUp) command to find the bottom of your current list and Application.Transpose to convert the vertical form entries into a horizontal row format.
Press the ALT + F11 keys on your keyboard to launch the Microsoft Visual Basic for Applications window.
In the VBA Editor, click on 'Insert' in the top menu bar and select 'Module' from the dropdown list.
Copy and paste the following code into the module window: Sub AppendData() Dim r As Long Dim arr As Variant arr = Application.Transpose(Worksheets("Input").Range("B3:B7").Value) With Worksheets("list") r = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Cells(r, 1).Resize(1, 5).Value = arr End With End Sub
Modify 'Input' to the name of your form sheet, 'B3:B7' to match your actual input cells, and 'list' to the name of your database sheet.
Close the VBA editor. Back in your workbook, press ALT + F8, select 'AppendData', and click 'Run' to transfer the current form inputs into the next blank row.
Use the TOROW Function for Dynamic Data Spilling
If you are using a newer version of Excel, you can use the TOROW dynamic array function to flip vertical inputs horizontally without writing code.
Build and Manage Data Entry Forms Seamlessly with WPS Spreadsheet
WPS Office Spreadsheet provides full support for VBA macros and advanced array formulas like TOROW. You can easily automate your customer data entries across thousands of rows without performance lags, entirely for free.
- 1. Download and install WPS Office: Get the official WPS Office software from the website and install it on your computer.
- 2. Open your Excel workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file containing the customer data form.
- 3. Enable macros and execute: Navigate to the Developer tab, ensure macros are enabled, and run your automated data appending scripts flawlessly.
Frequently Asked Questions
Why is my VBA macro overwriting existing table rows?
This happens if the code calculates the last row incorrectly. Ensure the column referenced in `Cells(Rows.Count, "A").End(xlUp).Row` contains data in every single row. If there are blank cells in column A, the macro might return a lower row number, causing it to overwrite existing entries. Consider using a column that is always filled as your reference.
Can I clear the form automatically after appending the data?
Yes. You can add a line of code at the very end of your VBA macro to clear the input fields. Before the `End Sub` line, insert `Worksheets("Input").Range("B3:B7").ClearContents`.
Does the TOROW function work in older Excel versions?
No, the TOROW function is exclusively available in Microsoft 365 and newer standalone versions. For older software versions, you should use the TRANSPOSE function entered as an array formula (by pressing Ctrl+Shift+Enter) or rely on the VBA macro solution.
