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!

 

[AKing_1st]

Thank you for the error checking!
I can't download XL2BB right now. I'll have to wait for the evening (in 13 hours circa).
Anyway the image already shows a row which has been moved from Table 1 to Table 2. What I would like to accomplish is to move the value of the column "J" (in this case "2") to the column "H" and change the word "ToBeAnalyzed" into "Analyzed" (this time without moving it from the "K" column, being a State).

 

[AKing_1st]

Hello @JoeMo I'm sorry, but it looks like I can't install XL2BB.
May you tell me if you could understand what I meant in my previous comments, please?

Anyway this is what I have now (it's Table 2):

And this is what I would like to obtain (still in Table 2):

 

[JoeMo]

If I understand correctly, you want to move and change cells in Table2. Are those cells in the row that was just moved to Table2? If yes, do you want the changes to that row to be incorporated in the code I posted to move a row from Table1 to Table2? If no, do you want a separate macro to make the changes, and do you want the macro to query the user as to which row in the table the changes should be made?

 

[AKing_1st]

You understood perfectly:
- the cells are in the row moved to Table 2;
- if possible, I'd like to move them with the macro you've already made. There's no need for another one. ?

 

[JoeMo]

OK, I've added some code to the macro I posted earlier. See if this does what you want. Assumes you have 13 columns in your table as shown in the images you posted.

Sub MoveRowToAnotherTable_4()
'removes a user-specified row from Table1 on Sheet1 and appends it to Table2 on Sheet2
'changes are then made to the appended row in Table2
Dim LO1 As ListObject, LO2 As ListObject, Sht1 As Worksheet, Sht2 As Worksheet, n As Variant, MoveCell As Range
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
    Set MoveCell = Intersect(.ListRows(.ListRows.Count).Range, .ListColumns(10).Range)
    With MoveCell
        .Copy Destination:=MoveCell.Offset(0, -2)
        .ClearContents
        .Offset(0, 1).Value = "Analyzed"
    End With
End With
Application.ScreenUpdating = True
End Sub

 

[AKing_1st]

First of all: thank you very much once again.
Actually the image was an example of mine. The table I must work on has hundreds of rows. Would it be possible to remove the 13 rows limiter?

 

[AKing_1st]

First of all: thank you very much once again.
Actually the image was an example of mine. The table I must work on has hundreds of rows. Would it be possible to remove the 13 rows limiter?

@JoeMo I'm sorry and an *diot. Your macro is perfect as it is!
I confused "columns" with "rows"...

Thank you very much for your patience and kindness.

 

[JoeMo]

@JoeMo I'm sorry and an *diot. Your macro is perfect as it is!
I confused "columns" with "rows"...

Thank you very much for your patience and kindness.

You are welcome - thanks for the reply.