Move Excel Table Rows by Owner Name with a VBA Macro
Question details
Create a VBA macro that reads the owner name in an Excel source table, moves each complete row to the matching owner destination, and removes the row from the source table after a successful copy.
- Product
- Microsoft Excel VBA and Excel tables
- Device & OS
- not provided
- Scenario
- A source table uses an Owned By column with a data-validation list to assign rows to team members, each with a destination worksheet and table.
- Observed behavior
- The required result is for every assigned row to be copied to its owner destination and then deleted from the source without skipping rows during processing.
Ensure every owner name in the source table exactly matches an existing destination worksheet name, and that each destination worksheet contains a table with the same column layout as the source table. Enable macros only for a workbook you trust.
Use a bottom-up VBA macro to route rows to owner tables
Use this approach when each owner has a worksheet named after them and a destination table that receives rows with the same columns as the source table.
The macro reads the owner from column B of SourceTable, finds a worksheet with that owner name, and appends the row to the first table on that worksheet. It loops from the last row to the first row, so deleting a source row does not change the position of rows that still need processing.
In Excel, select the source table and open Table Design > Table Name. Name it SourceTable. On each owner worksheet, create a destination table with the same headers and column order as SourceTable.
Press Alt+F11, select Insert > Module, and paste the following code into the new module: Sub MoveRowsByOwner() Dim wsSource As Worksheet Dim srcTbl As ListObject Dim srcRow As ListRow Dim wsDest As Worksheet Dim dstTbl As ListObject Dim newRow As ListRow Dim ownerName As String Dim i As Long Set wsSource = ThisWorkbook.Worksheets("Source") Set srcTbl = wsSource.ListObjects("SourceTable") For i = srcTbl.ListRows.Count To 1 Step -1 Set srcRow = srcTbl.ListRows(i) ownerName = Trim(CStr(srcRow.Range.Cells(1, 2).Value)) If ownerName <> "" Then Set wsDest = Nothing Set dstTbl = Nothing On Error Resume Next Set wsDest = ThisWorkbook.Worksheets(ownerName) If Not wsDest Is Nothing Then Set dstTbl = wsDest.ListObjects(1) On Error GoTo 0 If Not dstTbl Is Nothing Then Set newRow = dstTbl.ListRows.Add newRow.Range.Value = srcRow.Range.Value srcRow.Delete Else Debug.Print "No destination table found for: " & ownerName End If End If Next i End Sub
In the code, replace "Source" in ThisWorkbook.Worksheets("Source") with the actual worksheet name that contains SourceTable. Keep column B as the owner column, or change Cells(1, 2) to the appropriate table column number.
Return to Excel, press Alt+F8, select MoveRowsByOwner, and choose Run. Check that rows were added to the correct owner tables and that only successfully copied rows were removed from SourceTable.
Use explicit destination table names for stricter routing
Use this option when an owner worksheet contains more than one table or when the destination table is not the first table on the worksheet.
Route Owner-Assigned Table Rows with WPS Spreadsheet Macros
WPS Spreadsheet provides familiar table, data-validation, and macro tools for organizing owner-assigned work. Set up an Owned By list, maintain destination tables for team members, and use a compatible VBA-style macro workflow to copy completed assignments to the correct table.
- 1. Create the owner list: In WPS Spreadsheet, select the Owned By cells and choose Data > Data Validation to create a list of valid team members.
- 2. Format the source range as a table: Select the source data range, then use Insert > Table and assign a clear table name in the table settings.
- 3. Create matching owner destinations: Add a worksheet for each owner and create a destination table with the same headers and column order as the source table.
- 4. Open the macro editor: Use the Developer or Tools menu to open the macro editor, insert a module, and add your row-routing procedure.
- 5. Run the routing macro: Run the macro after assigning owners to move valid rows to their corresponding destination tables and retain unmatched rows for review.
Frequently Asked Questions
Why should the macro loop through the source table from bottom to top?
Deleting a ListRow changes the row positions below it. Using For i = ListRows.Count To 1 Step -1 ensures that deleting the current row does not cause the macro to skip the next row that must be processed.
What happens if an owner name does not match a destination worksheet?
With the validation check in the sample code, the macro does not delete that source row. It writes a message to the Visual Basic Editor Immediate window through Debug.Print so you can correct the owner name or create the missing destination.
Can I use a destination table name instead of a worksheet name?
Yes. Replace the worksheet lookup with a ListObjects lookup for a specific table name, or use a naming pattern such as tbl_OwnerName. This is useful when several destination tables exist on one worksheet.
Does the source and destination table need to have the same columns?
Yes, when using newRow.Range.Value = srcRow.Range.Value, both tables need the same number of columns in the same order. If their layouts differ, copy individual cells by column name instead.
How do I change the Owned By column from column B?
Change srcRow.Range.Cells(1, 2) in the macro. For example, use Cells(1, 3) when the owner value is in the third column of SourceTable, or retrieve the column by name with srcTbl.ListColumns("Owned By").Index.
