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.

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