+ Reply to Thread
Results 1 to 17 of 17

Moving rows to another sheet based on a certain field's value

  1. #1
    Registered User
    Join Date
    03-02-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Moving rows to another sheet based on a certain field's value

    Hi Excel experts,
    I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.

    Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.

    Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.

    Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.

    Can anyone assist?

    Thanks!
    Attached Files Attached Files
    Last edited by anthonymariani1982; 03-03-2011 at 01:05 PM.

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving rows to another sheet based on a certain field's value

    Hi anthonymariani1982

    Put this code in Worksheet WIP Track
    Please Login or Register  to view this content.
    If you have issues, let me know.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    03-02-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Moving rows to another sheet based on a certain field's value

    Thank you very much for your help!

    I have attempted to do so, by going to the VB option in the developer toolbar.

    I right click on the wip track worksheet, and choose "view code". A blank box appears, and I paste the code as written. I save the file, and click "run" .

    I am asked for a name - does it matter what I name it? I have attempted a few things, but that point I am stuck, and can't get any further.

  4. #4
    Registered User
    Join Date
    03-02-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Moving rows to another sheet based on a certain field's value

    Ok,
    I created a module for the worksheet in Visual Basic, and pasted the code. Next, went to Excel and in the Developer ribbon, clicked "Macros". Worksheet_Change does not show up, but when I type the name in, the "Run" option (previously greyed out) becomes active. I click "Run", and am returned to Visual Basic, with the message "Argument not optional".

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving rows to another sheet based on a certain field's value

    Hi anthonymariani1982

    Select "Complete" in Column Q of WIP Track and the row is moved to Complete.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-02-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Moving rows to another sheet based on a certain field's value

    Great, Thanks! Solved!

  7. #7
    Registered User
    Join Date
    09-11-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Moving rows to another sheet based on a certain field's value

    Hi Jaslake,

    Is there a way to tweak this VBA to have "complete" in another column (I'm adding my data validation list to column J), and so that it would work throughout the rest of the sheet? I noticed that it only works for the first few rows, but I would need it to also work for a couple of hundred rows.

    Any help would be GREATLY appreciated!!!

    Regards,

    ER

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving rows to another sheet based on a certain field's value

    Hi erod1214

    I should think the code could be "tweaked" however, you'll need to start your own Thread...see Rule #2
    Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.
    Let me know how I can help.

  9. #9
    Registered User
    Join Date
    02-05-2013
    Location
    minneapolis, MN
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Moving rows to another sheet based on a certain field's value

    I am wondering if there is anyway to tweak this so it can be commanded to transfer on a click of a button and to be used for dates. I would be using it for sending out auditing feeback on a daily basis and am wondering if there is a way to do this from a form with a text box I can use to enter the date?

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving rows to another sheet based on a certain field's value

    Hi brow1726

    Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  11. #11
    Registered User
    Join Date
    12-30-2013
    Location
    South Africa,Sandton
    MS-Off Ver
    Excel 2003
    Posts
    4

    Smile Re: Moving rows to another sheet based on a certain field's value

    Hi All,

    I trust that you are all well. Please assist me with the coding for the attached file. I need for the entire row from MTD to transfer to YTD when 'loaded' is selected from the dropbox in the status column(Q). If the status is changed from 'loaded' to any other status then i need it to move back to MTD. I tried editing the code to my requirement from this thread but it doesnt work correctly. Please help. Thanks.(please look at 64kb attachment)
    Attached Files Attached Files

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving rows to another sheet based on a certain field's value

    Hi Deo888

    Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  13. #13
    Registered User
    Join Date
    03-08-2014
    Location
    Ph
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Moving rows to another sheet based on a certain field's value

    test_sheet.xlsm
    Quote Originally Posted by jaslake View Post
    Hi anthonymariani1982

    Select "Complete" in Column Q ** WIP Track and the row is moved to Complete.
    Hi,
    I tried using the code on my sheet, and also added it to another tab so that entries can move back and forth the tabs, but I'm getting "run-time error 13, type mismatch". Details below. Please help. Thanks.

    Tab 1 name = Pending
    Tab 2 name = Final

    Pending tab code:
    Option Explicit
    Dim Flag As Boolean
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row

    If Flag = True Then Exit Sub
    If Not Intersect(Target, Range("D2:D" & LR)) Is Nothing Then
    If Target.Value = "Final" Then

    LR = Sheets("Final").Range("A" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy
    Sheets("Final").Range("A" & LR).PasteSpecial
    Flag = True
    Target.EntireRow.Delete
    End If
    End If
    Application.CutCopyMode = False
    Flag = False
    End Sub


    Final tab code:
    Option Explicit
    Dim Flag As Boolean
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row

    If Flag = True Then Exit Sub
    If Not Intersect(Target, Range("D2:D" & LR)) Is Nothing Then
    If Target.Value = "Pending" Then

    LR = Sheets("Pending").Range("A" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy
    Sheets("Pending").Range("A" & LR).PasteSpecial
    Flag = True
    Target.EntireRow.Delete
    End If
    End If
    Application.CutCopyMode = False
    Flag = False
    End Sub

  14. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Moving rows to another sheet based on a certain field's value

    Hi j_a

    Welcome to the Forum!!!

    You'll need to start your own Thread...see Rule #2
    Don't post a question in the thread ** another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

  15. #15
    Registered User
    Join Date
    05-28-2014
    Posts
    1

    Re: Moving rows to another sheet based on a certain field's value

    Hello everyone!

    I really hope you could help me with this.

    I am trying to copy candidates in POOL Sheet to either Onboarded, For Final or For Panel Interviews. But I hope they could retain their values on the POOL Sheet as well. I really hope you could help me with this. Thanks!

  16. #16
    Registered User
    Join Date
    06-18-2015
    Location
    New york, New York
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Moving rows to another sheet based on a certain field's value

    just saying, this doesnt work for me.

  17. #17
    Registered User
    Join Date
    02-22-2016
    Location
    Orlando, Florida
    MS-Off Ver
    2010
    Posts
    1

    Question Re: Moving rows to another sheet based on a certain field's value

    Hi, I am attempting to follow this thread to do something very similar. I downloaded this example and changed the appropriate column value to Complete; however, nothing happens. When I downloaded the file, I enabled editing and macros. Would you mind telling me what I could be doing wrong? I figure if I cannot get it to work in this example, then it may explain why it is not working in my actual document.

    Thank you so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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