Move entire rows from one table to another table

Marcia · #1 11-03-2021, 12:07 AM

Hi. I found the below code that copies selected rows to another sheet, outside of a table.

Code:

Selection.Copy Sheets("Edited").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

I would like the code to move the selected rows from table "tForEdit" of Sheet "ForEdit" to the first empty row of table "tEdited" in sheet "Edited".
Presently I am using this code. After running the code, I delete the selected rows, then resize the table to include the rows added.
An advance thank you for the usual help.

Guessed · #2 11-04-2021, 11:04 PM

I think this works one row at a time.

Code:

Sub MoveARow()
  Dim aLRow As ListRow, activeListCells As Range
  Dim iRowSel As Integer, iRowList As Integer, iSelRow As Integer
  Dim aLO As ListObject, aLO2 As ListObject
  Set aLO = ActiveWorkbook.Sheets("ForEdit").ListObjects("tForEdit")
  Set aLO2 = ActiveWorkbook.Sheets("Edited").ListObjects("tEdited")
  iRowSel = Selection.Row
  iRowList = aLO.Range.Row
  
  Set activeListCells = Intersect(aLO.DataBodyRange, Selection)
  If activeListCells Is Nothing Then
    MsgBox "List row not selected"
  Else
    iSelRow = iRowSel - iRowList
    Set aLRow = aLO.ListRows(iSelRow)
  End If
  aLO2.ListRows.Add
  aLO2.ListRows(aLO2.ListRows.Count).Range.Value = aLRow.Range.Value
  aLRow.Delete
End Sub

Marcia · #3 11-05-2021, 02:42 AM

Thank you Andrew. It works one row at a time and we have about 175,000 rows to edit, group and move to the tEdited table. Is there a way to make the code move multiple, contiguous rows?

Guessed · #4 11-10-2021, 04:42 AM

This code should work if your selection is of complete table rows. It also works with discontinuous selections.

Code:

Sub MoveARow()
  Dim activeListCells As Range, aLO As ListObject, aLO2 As ListObject
  
  Set aLO = ActiveWorkbook.Sheets("ForEdit").ListObjects("tForEdit")
  Set aLO2 = ActiveWorkbook.Sheets("Edited").ListObjects("tEdited")
  Set activeListCells = Intersect(aLO.DataBodyRange, Selection)
  If activeListCells Is Nothing Then
    MsgBox "List row not selected"
  Else              'assumes the selection is of complete table rows
    aLO2.ListRows.Add
    activeListCells.Copy aLO2.ListRows(aLO2.ListRows.Count).Range.Cells(1, 1)
    activeListCells.Delete
  End If
End Sub

Marcia · #5 11-16-2021, 05:24 PM

This is perfect Andrew. Many many thanks.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Deleting Blank Space between table heading and table rows	Pete Jones	Word Tables	5	01-22-2018 04:11 PM
Duplicating one or more table rows or an entire table with content controls	kevinbradley57	Word VBA	10	08-17-2017 02:13 PM
Move table cell contents from one table to another table or cell in same table	donaldadams1951	Word VBA	4	02-04-2015 03:54 PM
Is it possible to copy non-contiguous rows of a Table and paste them as a separate Table in Word?	Joey Cheung	Word Tables	1	08-12-2014 05:15 PM
Grouping table rows to prevent individual rows from breaking across pages	dennist77	Word	1	10-29-2013 11:39 PM