+ Reply to Thread
Results 1 to 20 of 20

Move Row from a Table in one sheet to a Table in another sheet depending on cell value

  1. #1
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Hi,

    I am struggling to get a vba macro to work which would move a row from one table to another which is on a different sheet in the same workbook, depending on the value. I will either run this as a worksheet change private sub, or an update button. The workbook attached is a representative workbook due to commercial sensitivity!

    Please Login or Register  to view this content.
    It works well so far, but I am trying to get it to only make this check if the active cell is in the table column "Status" . Also, I can work this through without using tables quite easily, however tables work much better in other aspects of the workbook.

    The action I have set is to simply select the row, I could work towards copying and deleting the row from the active table although if anyone can give me the full answer I'd really appreciate it!
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Thanks very much for your help,

    I've found an issue or 2 with the code above, when the table on sheet1 is empty, and then I move something into sheet1 from sheet2, because it is in a table format (again I don't particularly want to deviate from that) it puts it below and outside of the table, so it ends up with a blank row before the data begins again.

    It also has a runtime error if I use the delete row function in the table.

    Lastly, because this uses "target." and "Workbook_SheetChange" how would I modify this to run using a sub run from a button? i.e. I would like to update 3 of the rows and then hit a button that moves them all with a For loop.

    Huge thank you for the help so far!!!

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    I tested the macro on the file you posted and it worked properly. What works on a sample most often doesn't work on the actual data. Can you replace any confidential data with generic data and attach a copy of your file? Please include a sheet with an empty table. You don't necessarily need all the data, just enough to allow the macro to be tested.

  5. #5
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Hi Mumps,

    I used the data that I uploaded originally. I can alter to suit my data and I'm happy to do so once i've got it working with the data originally uploaded. Are you saying you don't get an error when you use the delete row function? (I mean the **table** function of delete row i.e. right click, delete, table rows).

    Also, I fully expect with my data for there to be zero lines on some sheets, until the data is moved from other sheets. It again doesnt work on the original uploaded data, I get a blank line and then the data.
    Thanks again, I'm learning a lot as usual!

    Pete

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    I get an error if I manually delete a row. The macro copies the data to the appropriate sheet and then deletes the original. Are you saying that you sometimes have to delete a row manually? This version of the macro should take care of the blank tables.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    I could add an additional sheet containing discarded rows, i.e. have a deleted sheet, and a drop down option to move to the deleted sheet. That would maintain the data as well.

    I will be locking the workbook down significantly for the people that will be using it so may lock out the delete function.

    Is there a way to have it done with a button and not reliant on "target." and not using "workbook change"

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    If you want to use a button, you would need a button on every sheet and you would have to manually select a cell in column B of the row you want to copy before running the macro by clicking the button. It is much more efficient to use to use a "workbook change" event because you don't need any buttons at all. If it would make it easier, you could have the macro run automatically by right clicking or double clicking a cell in column B instead of clicking a button. Would that work for you? If by "locking the workbook down significantly" you mean protecting the sheets with or without a password, I would need to know that. Please advise. It would be ideal if you could post a copy of a de-sensitized workbook.

  9. #9
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Completely agree its more efficient with a workbook change event, however:

    I have uploaded a barebones version of the working tracker, and changed the macro to suit. in column X are comments against each item that will form a narrative as the status gets changed.

    I am trying to work a way of having an input box, a button with a macro that takes the input, puts a time and date stamp on it, a user name, and adds it to the cell containing the narrative. In this case it would make sense to have both the move to another sheet function and the update comments function in the same button.

    I've attached the updated file. Thanks for your help once again!
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    I am using the following code for time and date stamping the comments and transferring them into an adjacent column. Unfortunately "target.clear" doesn't go well at the end of the sub and gives a stack error.

    I have just started to look into protecting the workbook and it seems to be rather more difficult than I thought it would be!

    Please Login or Register  to view this content.

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Are you saying it's OK to use a Worksheet_Change event? I am getting somewhat confused on what you want to do with the comments. Based on the macro you posted in Post #10, you enter a new comment in column Y and then concatenate it with the existing comment in column X, the username and the time stamp. You had mentioned that you wanted an input box to enter the new comment. Is that no longer the case? Please clarify in detail. Is the previous Worksheet_Change macro now working properly for you?

  12. #12
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Hi Mumps,

    I'm trying to find the best way to do the job! And I am trying to work on it and learn more while we talk so I apologise if it seems the goalposts are moving.

    The previous worksheet_change macro works, although it gives some runtime errors here and there depending on what I'm doing. Which is fine - I can learn to live with a few errors!

    I have a tracker attached in Post #9, The information in the majory of the columns won't change (orange columns) in the life of the tracker, but some will (green columns). (please also note that the column tagged on the end is orange by accident, I still intend for this to be the editable comment box.)

    There will be around 10 people using this tracker on a fairly regular basis, a couple of times a day for around 3 years or so and I want to record thier comments with time and date, but not allow them to edit the history of the comments so I can keep an accurate history within the tracker. I originally envisaged a portion of the worksheet (the top 17 rows) as an area in which I could have them enter some comments, update the status, update a few of the other green columns click submit and the item would save those comments and move the row to the correct tab.

    Most of this, with your help!, has now been achieved without using a macro change button. and I think that with one or 2 tweaks, such as clearing the cell after the comment has been saved, I won't need to have a comment box and submit button.

    If there is an easier way you can see that I can achieve any of this I am all ears. The protection is simply so I don't have to worry about any of the data in the orange columns, or the historical comments, being edited or wiped. I have made simpler trackers before, and after a month it's almost unreadable with the amount of different coloured cells, comments written all over the place, with no real understanding of who said what and when .

    Thanks so much for your time on this, as I said, I'm learning a lot!

    Pete

  13. #13
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Here is my understanding of what you want to do. Please correct me if I have misunderstood:
    -A user enters a comment in column Y.
    -After the comment is entered, it is concatenated to the existing comment in column X, adding the username and the time stamp.
    -The newly entered comment in column Y is cleared, ready for the next input.
    -The data in column X and all the orange columns is to be locked so it can't be edited.
    Is this correct?
    You have to keep in mind that if many comments in any row in column Y are entered, the amount of data in column X will grow accordingly.
    How is the data in all the orange columns entered. If those columns are locked, you won't be able to enter any data.

  14. #14
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Quote Originally Posted by Mumps1 View Post
    Here is my understanding of what you want to do. Please correct me if I have misunderstood:
    -A user enters a comment in column Y.
    -After the comment is entered, it is concatenated to the existing comment in column X, adding the username and the time stamp.
    -The newly entered comment in column Y is cleared, ready for the next input.
    -The data in column X and all the orange columns is to be locked so it can't be edited.
    Is this correct?
    Completely correct

    Quote Originally Posted by Mumps1 View Post
    You have to keep in mind that if many comments in any row in column Y are entered, the amount of data in column X will grow accordingly.
    Thats absolutely fine, althought there will be over a thousand records, I expect the comments would be limited to perhaps 5 or 6 per record and therefore quite managable.

    Quote Originally Posted by Mumps1 View Post
    How is the data in all the orange columns entered. If those columns are locked, you won't be able to enter any data.
    The data in the orange columns will be entered at the start and after that there will be no need to enter more. The tracker is tracking the completion of a large portion of work that will not grow, and if it does I would have write access and therefore can add manually.

  15. #15
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Try the attached file. I have locked all the appropriate columns and protected the sheets with the password "mypassword". Change the password (2 occurrences) in the macro in the code module for ThisWorkbook to suit your needs. The macro takes care of moving the rows and populating column X.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Perfect. One last question I think!

    Within

    Please Login or Register  to view this content.
    Would this be the correct code to add the user, time, date and status change to the comments box before moving the row?

    Please Login or Register  to view this content.

  17. #17
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    I think that should work.

  18. #18
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Cheers for all the help!

  19. #19
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    My pleasure.

  20. #20
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Move Row from a Table in one sheet to a Table in another sheet depending on cell value

    Link to new post!

    Hi Mumps!

    I have a further query on the VBA code you helped me with, opened a new thread link above.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Move row from one table to another on the same sheet based on cell value
    By Ghostrider757 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-30-2022, 12:48 AM
  2. Move Table to another Sheet
    By ionelz in forum Excel General
    Replies: 1
    Last Post: 10-12-2018, 10:44 PM
  3. Replies: 2
    Last Post: 01-30-2017, 03:25 PM
  4. Populating a table for a list (another sheet) depending on a key
    By drinu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-18-2014, 11:05 AM
  5. Replies: 9
    Last Post: 04-15-2014, 08:12 PM
  6. [SOLVED] Move row to different table depending on cell value
    By Marco-Kun in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 12-18-2011, 06:05 PM
  7. Excel 2007 : How to move table data from one sheet to another?
    By aboveliquidice in forum Excel General
    Replies: 1
    Last Post: 08-18-2011, 10:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1