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

[AKing_1st]

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.

Example.xlsx

Microsoft Excel Workbook

1drv.ms

Thank you very much!

 

[JoeMo]

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.

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]

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]

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.

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]

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]

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]

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]

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]

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]

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.

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