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!
 

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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).
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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):

joe.jpg


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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,410
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,410
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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
 
Solution

AKing_1st

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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. ;)
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,410
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

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