VBA & Macro Problems

How to Move Selected Excel Rows Between Tables Using a VBA Macro

Phi Hung Vo Oct 8, 2026 869 views

Question details

The user needs a VBA macro that can move multiple selected rows from one table to a destination table on another worksheet based on a drop-down value, and then delete the original source rows.

How to Move Selected Excel Rows Between Tables Using a VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Distributing tasks or data entries from a master table to individual team member worksheets, requiring data to be properly inserted into the destination tables without leaving blank rows behind.
Observed behavior: Previous macro attempts resulted in pasting rows below the destination table instead of inside it, and errors occurred when trying to process and delete multiple selected rows at once.

Before you start

Ensure that macros are enabled in your spreadsheet software and always save a backup copy of your workbook before running VBA scripts that permanently delete or move source data.

Solution 1Recommended

Use ListObjects and a Bottom-Up Loop in VBA

This solution iterates through selected rows backwards, utilizes ListObjects to insert data directly into the destination table boundaries, and safely deletes the source rows.

To ensure data is added inside the table rather than below it, the macro must specifically reference the destination 'ListObject' and use the 'ListRows.Add' method. Furthermore, when deleting rows in VBA, you must loop from the bottom upward to prevent row index shifting, which causes skipped rows.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet program.

Insert a New Module

Right-click on your workbook name in the Project Explorer panel, select 'Insert', and click 'Module' to create a blank script window.

Create the Bottom-Up Loop

Write a For loop that iterates through the Selection.Rows backwards using 'Step -1'. For example: 'For i = Selection.Rows.Count To 1 Step -1'.

Reference the Destination ListObject

Inside the loop, read the target worksheet and table name from your drop-down column. Use 'Set destTable = Worksheets(targetSheet).ListObjects(targetTable)' to establish the destination.

Add and Copy the Row

Use 'destTable.ListRows.Add' to create a new row inside the destination table, then copy the values from 'Selection.Rows(i)' into the newly created table row.

Delete the Source Row

After the data is successfully copied, delete the original row using 'Selection.Rows(i).EntireRow.Delete' before the loop proceeds to the next iteration.

Use ListObjects and a Bottom-Up Loop in VBA

Table Integration: Using the ListRows.Add method guarantees that any formatting, formulas, or table styling applied to the destination table will automatically apply to the newly moved row.

Advanced Spreadsheet Automation

Automate Table Management with WPS Spreadsheets

WPS Spreadsheets provides comprehensive support for VBA macros, allowing you to easily automate complex data movements, manage ListObjects, and manipulate multiple tables without compatibility issues.

1. Open your macro-enabled workbook: Launch WPS Office and open your .xlsm file containing the data tables.
2. Access the Developer tab: Navigate to the Developer tab on the ribbon and click on the 'VBA Editor' icon.
3. Implement the script: Paste your ListObjects manipulation macro into a new module and customize your table references.
4. Execute the macro: Select the rows you wish to move, then run the macro to automatically transfer the data and clean up the source table.

Fully compatible with Microsoft Excel formats, including .xlsm macro-enabled workbooksRobust VBA engine for running and editing complex scripts nativelyLightweight installation with high processing speed for heavy data manipulationFamiliar user interface requiring zero learning curve for Excel users

Frequently Asked Questions

Why is my VBA macro pasting rows below the destination table?

This happens if your macro inserts the copied data into a standard blank worksheet row instead of interacting with the table object. To fix this, your VBA code must reference the destination table as a 'ListObject' and use the 'ListRows.Add' command so Excel knows to expand the table boundaries.

How can a macro delete multiple selected rows without skipping any?

When you delete rows from top to bottom, the remaining rows shift upward, changing their index numbers and causing the standard loop to skip alternating rows. You must process and delete the rows using a reverse loop (e.g., looping backwards from the total count down to 1 using 'Step -1').

Can I read the destination worksheet name dynamically from a cell value?

Yes, you can declare a string variable in your VBA code and assign it the value of the specific cell containing the drop-down selection. You can then pass this variable into your 'Worksheets()' collection to dynamically target the correct destination.

Excel & Data Problems