• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to move any row from a Table to a Sheet?

E

Efreet61

New Member
Hello everyone,
I was wondering if it is possible to move any row (it mustn't necessarily be the first or the last one) from a table to another sheet.
If it is, I would appreciate to learn how to do it, with your help.

I attached for you an example I made: I'd like to be able to move any row from the table "db_ToBeAnalyzed", which is in Sheet2, to Sheet1.
As you can see, in Sheet1 there isn't the same kind of Table like the one in Sheet2. That is because I will have to join some cells in the future... but this is not important, because I am just interested in moving rows.

I hope I expressed myself well!

Thank you in advance!
Efreet61

Mod Edit: Cross-post linked moved up here for visiblity

Macro which moves any row from a Table to a Sheet and udpates content?

Hello everyone, I would like to make a macro which lets me move one row (it mustn't necessarily be the first or the last one) from the Table set in"Sheet2" to the one set in "Sheet1". As you can see, the table in "Sheet1" does not have the Table Format like the one in ""Sheet2. That is...
www.excelforum.com
 

Attachments

  • Move row.xlsx
    15.3 KB · Views: 10
Last edited by a moderator:
Efreet61
It's possible...
to move row,
You should know - which row to move as well as to which row to?
a) 'record' source rows data
b) add a new row to target table as You should know
c) paste recorded data as those are in target table
d) delete source tables recorded (a) row
 
You may use Filter formula like ... =FILTER(db_ToBeAnalyzed,db_ToBeAnalyzed[State]="To be Analyzed","No Data found")
 

Attachments

  • Move row.xlsx
    17.5 KB · Views: 2
Hello everyone and thank you very much for your answers.
I apologize for not being clear about what I would like to achieve. I must stop asking questions when I am tired...
I would like to make a macro which lets me move one row from a table to another sheet. Also, I would like to change the content of the cells (I colored them in my example file).

Unluckily I'm not on my computer, right now. Maybe you have already answered to my question.

Cheers
Efreet61
 
Efreet61
... did You read my #2 reply?
... there were a question too like -
How do You let Your Excel know - eg which row You would like to move to other table?
 
vletm said:
Efreet61
It's possible...
to move row,
You should know - which row to move as well as to which row to?
a) 'record' source rows data
b) add a new row to target table as You should know
c) paste recorded data as those are in target table
d) delete source tables recorded (a) row
Click to expand...

Hello @vletm ,
I recorded a macro with which I selected a row in the Table in "Sheet2" and moved it in "Sheet1":

Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A5:M5").Select
    Application.CutCopyMode = False
    Selection.Cut
    Sheets("Sheet1").Select
    Range("A14").Select
    ActiveSheet.Paste
    Range("B14:M14").Select
    Selection.Cut Destination:=Range("G14:R14")
    Range("O14").Select
    Selection.Cut
    Range("M14").Select
    ActiveSheet.Paste
    Range("P14").Select
    ActiveCell.FormulaR1C1 = "Analyzed"
    Range("Q14").Select
End Sub

Obviously it just works with the row which is originally set at "A5"; also, it can only be moved to the row "A14" in "Sheet1".
May you tell me how to modify the code so that I can choose any row and move it to the last row of the table in "Sheet1", please?

P.S. Forgive my English, please. I know I can be incomprehensible, sometimes...
 
Efreet61
You asked about to move, but Your above code seems to missed that feature.
My sample has [ MOVE ]-button in Sheet2.
Before click it - activate row, which You would like to move to Sheet1
Click [ MOVE ]-button
This sample includes Your codes 'Analyzed'-modification.
 

Attachments

  • Move row.xlsb
    22.8 KB · Views: 16
Hello @vletm ,
with "move" I meant to cut and paste the row, but I see you understood perfectly as well!
Your macro is very easy to understand, I didn't know you could command Excel to "beep"! :DD

The only questions I have are:
- how can I move (cut and paste) the number of modules that must be analyzed to the right cell?
- is it possible to keep the original format in the W.O. cell ("0.xxx" and not 0,xxx)?

Thank you!
 
This is a wild cross posting as you was yet warned on another forum !​
​
See forum rules :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
 
@vletm @Efreet61

I would ask you all to take a breath before writing your next reply and try to have an open mind. It seems like an issue is escalating over what was probably an innocent mistake. Yes, Efreet should have included the cross-post link, but has admitted that mistake. Efreet, if you can please edit the first post in this thread with the link to the other thread, that would be appreciated. Vletm, for a non-native English speaker, your replies are coming off a bit strong and abrupt. I can appreciate that as a prolific user on the forum we end up seeing the same problem over and over, and it can be tempting to use abbreviations and short replies, but that can sometimes have the opposite of the intended effect. I am assuming the best and hoping this was just an honest misunderstanding.

So, if this thread can focus back on the XL issue being asked, wonderful. If it continues to debate rule and personal intent, I'm afraid I will have to lock this thread. If you have questions/concerns, feel free to send me a direct message.
 
Last edited:
Hi Luke !​
​
On another forum Efreet61 was warned several times to respect the rules as instead of doing what any forum expects​
he created a duplicate thread ! So he is at the border to be banned for a week at least …​
​
My bad : error on the person, it's someone else, apologies.​
​
 
Luke M said:
@vletm @Efreet61

I would ask you all to take a breath before writing your next reply and try to have an open mind. It seems like an issue is escalating over what was probably an innocent mistake. Yes, Efreet should have included the cross-post link, but has admitted that mistake. Efreet, if you can please edit the first post in this thread with the link to the other thread, that would be appreciated. Vletm, for a non-native English speaker, your replies are coming off a bit strong and abrupt. I can appreciate that as a prolific user on the forum we end up seeing the same problem over and over, and it can be tempting to use abbreviations and short replies, but that can sometimes have the opposite of the intended effect. I am assuming the best and hoping this was just an honest misunderstanding.

So, if this thread can focus back on the XL issue being asked, wonderful. If it continues to debate rule and personal intent, I'm afraid I will have to lock this thread. If you have questions/concerns, feel free to send me a direct message.
Click to expand...

Hello @Luke M,
on saturday vletm and I had a brief conversation on pm and I think we may have finally understood each other.
About my first post, unluckily I don't seem to be able to edit it anymore. Otherwise I would have done it last week.
I did insert the link into my answer to @Marc L though, but it seems it was deleted...
Here it is, anyway: Macro which moves any row from a Table to a Sheet and udpates content? (excelforum.com)

Also, I would relly like to know where I was warned "several times" to respect the rules, as @Marc L stated.
 
My bad, accept all my apologies as I had to investigate back in the other forum :​
it's someone else in this case, at the same time on same forums​
and unfortunately I made the confusion between you and the other …​
Sorry for the inconvenience, post #13 amended.​
​
 
I'm not sure what you are expecting so if you can well elaborate it could be easier to help you …​
​
 
Hello @Marc L ,
what I would like to make is a macro which lets me select a row from the Table in Sheet2 and "move" it to the end of the Table situated in Sheet1 by pressing a button.
With "moving" I mean copy > paste in Sheet1 > clear, as it was rightly suggested to me by @vletm.
Also, I would like to change the content of the cells (I colored them in my file, hoping it's understandable enough).

This is a sample, so Sheet1 has got just 2 merged rows, but in reality I have hundreds of them and not all of them have 12 merged rows. Some have more.

Thank you and please tell me if something is unclear. I will correct the text as soon as possible to make it more understandable.
 
So you are confident enough with your Excel / VBA skills to amend the below VBA demonstration​
for what you forgot to explain but as it's at beginner level, you could do it if necessary …​
​
According to your attachment as a beginner starter to paste to the Foglio2 (Sheet2) worksheet module​
(just select a single cell in the row 'to move' to the previous worksheet) :​
Code: 
Sub Demo1()
    With ListObjects(1)
        If Selection.CountLarge > 1 Or Intersect(Selection, .DataBodyRange) Is Nothing Then Beep: Exit Sub
    With Foglio1
        With .Cells(.Rows.Count, 1).End(xlUp):  R& = .Row + .MergeArea.Rows.Count:  End With
        Cells(Selection.Row, 1).Copy .Cells(R, 1)
        [B:M].Rows(Selection.Row).Copy .[G:R].Rows(R)
       .Cells(R, 13).Value2 = 1
       .Cells(R, 16).Value2 = "Analyzed"
    End With
       .ListRows(Selection.Row - .Range.Row).Delete
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Hello @Marc L,
thank you for your help.
I think I understood what I forgot to specify, because I erroneously took it for granted: when I move a row from Sheet2 to Sheet1, the number of analyzed modules should coincide with that of the modules to be analyzed (I refer to when the row is still in Sheet2, column "J"); once in Sheet1 there is no longer any need for the number of modules to be analyzed.

Once this is fixed, the macro is perfect.
 
Hello everyone,
I fear I'm not able enough to fix the macro in #19...
May you please help me to copy the number of modules to be analyzed (column "J" in Sheet2) into the column of analyzed modules, once the row is moved ("M" in Sheet1)?

The result should be: "x" modules to be analyzed in Sheet2 (column "J") should become "x" modules analyzed, after moving the row to Sheet1 (column "M"). Also, once in Sheet1 "x" modules to be analyzed (column "O") should be cleared, leaving said column empty.

Thank you very much
Efreet61
 
  • To copy use SourceRange.Copy DestinationRange …

  • For the text use the Replace function just replacing "to be " by "" …
 
You must log in or register to reply here.
Back
Top