How to Add Rows from One Excel Table to Another with VBA
Question details
The user needs a VBA script to dynamically copy rows from a source table into a destination table while resolving and avoiding the 'Subscript out of range' error.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the transfer of data between two structured Excel tables using macros without encountering collection reference errors.
- Observed behavior
- When attempting to copy rows using VBA, a 'Subscript out of range' error is triggered because the script fails to locate the specified worksheets or table objects.
Verify that your data is formatted as official Excel Tables (Insert > Table). Note down the exact Worksheet names and Table Design names, as mismatching these is the primary cause of reference errors.
Use Explicit ListObject References and a Loop
The most reliable way to copy rows between tables is to explicitly declare your ListObjects and loop through the source rows to add them to the destination.
The 'Subscript out of range' error occurs when Excel cannot find the table or sheet referenced in your code. Explicitly defining the path to the ListObject ensures the macro targets the exact elements.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window, then click Insert > Module from the top menu.
Define your source and destination tables as ListObjects by adding: Dim TaskTable As ListObject, ActionQuery As ListObject, and Dim i As Long.
Assign the exact paths to your tables. Example: Set TaskTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("TaskTable"). Replace 'Sheet1' and 'TaskTable' with your actual names.
Write a For loop that iterates through the source rows: For i = 1 To ActionQuery.ListRows.Count. Inside the loop, append data using: TaskTable.ListRows.Add.Range.Value = ActionQuery.ListRows(i).Range.Value.
Close the loop with 'Next i' and 'End Sub'. Press F5 or click the Run button to execute the macro and transfer the table rows securely.
Copy Only Visible Rows from a Filtered Table
Modify the ListObject loop to detect and skip hidden rows, which is useful if your source table has active data filters applied.
Automate Table Data Transfers with WPS Spreadsheet Macros
WPS Office fully supports advanced VBA macros, allowing you to run your ListObject scripts to manage, copy, and automate structured table data smoothly.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your source and destination tables.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor' to open the macro environment.
- 3. Execute the Code: Insert a new module, paste your explicit ListObject VBA script, and click 'Run' to seamlessly copy the table rows without errors.
Frequently Asked Questions
What exactly causes the 'Subscript out of range' error in Excel VBA?
This error (Run-time error 9) typically occurs when your VBA code tries to access an object, like a worksheet or a ListObject table, that does not exist, has been renamed, or is spelled incorrectly in your script.
How do I find the exact name of my Excel table for the macro?
Click anywhere inside your table, navigate to the 'Table Design' tab on the ribbon, and look at the 'Table Name' box in the Properties group on the far left. You must use this exact string in your VBA code.
Can I clear the destination table before copying the new rows over?
Yes. Before starting your For loop in the VBA script, add the line: TaskTable.DataBodyRange.Delete. This will empty the destination table entirely before appending the new source rows.
