How to move any row from a table to another one?

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,
I'd like to know if it's possible to use a macro to be able to move any row (it mustn't necessarily be the first or the last one) from a table to another one, set in another sheet.

If it is possible, I'd appreciate to learn how to do it, too, with your help.

I link you an example I made: I'd like to be able to move any row from the table "db_daAnalizzare", which is in the sheet "DB_ToBeAnalyzed", to the table "db_Analizzati", which is in the sheet "DB_Analyzed.


Thank you very much!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Sorry, I don't download from the internet, but here's an example you can adapt to your needs by changing table and sheet names to suit your data. I'm assuming that by "moving" a row you mean remove the row from one table and insert or append it to another table.
VBA Code:
Sub MoveRowToAnotherTable1()
'removes a row from Table1 on Sheet1 and inserts it above a specified row on Table2 on Sheet2
Dim LO1 As ListObject, LO2 As ListObject, Sht1 As Worksheet, Sht2 As Worksheet
Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")
Set LO1 = Sht1.ListObjects("Table1")
Set LO2 = Sht2.ListObjects("Table2")
Application.ScreenUpdating = False
'Examlple: move list row 2 from Table 1 and insert it above list row 5 on table 2
With LO2.ListRows(5)
    .Range.Insert
    With LO1.ListRows(2)
        .Range.Cut Destination:=LO2.ListRows(5).Range
        .Range.Delete shift:=xlUp
    End With
End With
Application.ScreenUpdating = True
End Sub
 

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello @JoeMo and thanks for your reply!
Your code does move the 2nd row from the first table and moves it to the 5th row of the second table... but I would like to be able to choose which row to move and put it in the end of the first table.
Also, my bad, I should say that these tables have many rows and are dynamic.

Would you still like to help me?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Yes, I'd still like to help you :).
In the macro below, you specify when prompted the LIstRow you want to move from Table1. The first ListRow (List Row 1) is the row beneath the header row and so on ...
The specified row will be moved and appended to Table2.
Change Table names and Sheet names to suit.
VBA Code:
Sub MoveRowToAnotherTable_2()
'removes a user-specified row from Table1 on Sheet1 and appends it to Table2 on Sheet2
Dim LO1 As ListObject, LO2 As ListObject, Sht1 As Worksheet, Sht2 As Worksheet, n As Variant
Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")
Set LO1 = Sht1.ListObjects("Table1")
Set LO2 = Sht2.ListObjects("Table2")
Application.ScreenUpdating = False
'append new row to LO2, cut a row from LO1 and move to appended row on LO2
With LO2
    .ListRows.Add
    n = InputBox("Which ListRow in table 2 do you want to move?")
    If n = "" Then Exit Sub 'user clicked Cancel
    With LO1.ListRows(Val(n))
        .Range.Cut Destination:=LO2.ListRows(LO2.ListRows.Count).Range
        .Range.Delete shift:=xlUp
    End With
End With
Application.ScreenUpdating = True
End Sub
 

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Amazing, thank you for your kindness!
I noticed that, if you write a row number which doesn't belong into the index, the macro still adds an empty row to the second table.
It's not a big problem, though!
May I ask you what I should do if I also wanted to move the values of column "J" to "H" and tranform the word "To be Analyzed" in column "K" into "Analyzed", please?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Amazing, thank you for your kindness!
I noticed that, if you write a row number which doesn't belong into the index, the macro still adds an empty row to the second table.
It's not a big problem, though!
May I ask you what I should do if I also wanted to move the values of column "J" to "H" and tranform the word "To be Analyzed" in column "K" into "Analyzed", please?
You are welcome.
I did not build in any error checking, but that could easily be done to avoid the problem you cite.

I think your new question would be a candidate for a new thread as it's not clear to me that it relates to the current thread.
 

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I don't know...
In my opinion it belongs to this thread, because I'm still talking about the values of the row I would move. Also, I don't think it would be that right (for you) to start a new thread with your code, asking for something to add into it.
Let me know what you think about it. If you prefer to see a new thread, I shall open it up. 😉
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I don't know...
In my opinion it belongs to this thread, because I'm still talking about the values of the row I would move. Also, I don't think it would be that right (for you) to start a new thread with your code, asking for something to add into it.
Let me know what you think about it. If you prefer to see a new thread, I shall open it up. 😉
I don't understand what you want to achieve. Can you post some sample data using XL2BB and explain a bit more?
 

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
joe.jpg


Maybe this is better. ;)
Once the row is in the final table, I would like to move its values set in the "J" column into the "H" column; it would be nice to manage to change the state of the "K" column to "Analyzed", too.

Please, let me know if this is better. Otherwise I'll download XL2BB.

Edit: If it's not too much, may I see the error checking too, please? It will surely be useful, in the future.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Please use XL2BB and show the result you expect as well.

Here's an example of error checking that handles the error where the user enters a Listrow number that doesn't exist in Table1.
VBA Code:
Sub MoveRowToAnotherTable_3()
'removes a user-specified row from Table1 on Sheet1 and appends it to Table2 on Sheet2
Dim LO1 As ListObject, LO2 As ListObject, Sht1 As Worksheet, Sht2 As Worksheet, n As Variant
Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")
Set LO1 = Sht1.ListObjects("Table1")
Set LO2 = Sht2.ListObjects("Table2")
Application.ScreenUpdating = False
'append new row to LO2, cut a row from LO1 and move to appended row on LO2
With LO2
    .ListRows.Add
Again: n = InputBox("Which ListRow in Table1 do you want to move?")
    If n = "" Then
        LO2.ListRows(LO2.ListRows.Count).Delete
        Exit Sub 'user clicked Cancel
    End If
    If Val(n) < 1 Or Val(n) > LO1.ListRows.Count Then
        MsgBox "No ListRow number " & Val(n) & " in Table1 - try again"
        GoTo Again
    End If
    With LO1.ListRows(Val(n))
        .Range.Cut Destination:=LO2.ListRows(LO2.ListRows.Count).Range
        .Range.Delete shift:=xlUp
    End With
End With
Application.ScreenUpdating = True
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top