No announcement yet.

Automatically Move & Sort Row Based On Cell Value

  • Filter
  • Time
  • Show
Clear All
new posts

  • Automatically Move & Sort Row Based On Cell Value

    Hello, I'm a VBA-noob and don't know much about coding in general so I figured I should ask someone who does. I hope someone with more knowledge than me, could help me sort this mess out

    I have a simple projectlist on sheet1 formatted like this:
    Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status.
    On sheet2 I have the same columnheaders.

    What I would like to do, is that if Status changes to "Done",
    * the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything
    * the now empty row on sheet1 should be deleted (so there're no empty rows)
    * the data on sheet2, should be sorted with D as first key and C as second.

    Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1.

    My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour?

    I only know small bits of VBA but my idea is this:
    Write a sub that searches D2:D200
    for "done" or "Done", selects the cell that contains it and use something like Target.EntireRow.Cut Sheets("sheet2")."<sheet2.first empty row>"
    and then Target.EntireRow.Delete...

    I don't know enough VBA-coding, but can "<sheet2.first empty row>" be like a global variable derived from another sub and always present, or do I need to call that sub from the search and move sub? If so, should I do it before it searches Status for a match?

    Is there a way to insert the row on ie A2, pushing the existing rows on sheet2 down a notch? That would be the best way.

    Should I put number of the Status matching row on sheet1 in a variable, so that I can use that row number to delete the row, or will it work to both cut/paste and delete it based on Target? I'm afraid that the row loses it's Target status once I paste it into another sheet? When I've tried this, it seems to sometimes delete the row above instead of the correct one.

    I have a working sort sub that looks like this:
    Sub SortS2()
        With Worksheets("Sheet1")
            .Range("A:D").Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        End With
    End Sub
    Should I have a separate sub for this and call it from the other, or should I just paste this code into the big sub?
    Last edited by NN74; September 21st, 2008, 00:20.

  • #2
    Re: Automatically Move &amp; Sort Row Based On Cell Value

    Keep it simple. No VBA required.


    Sheet2 should not be needed.

    Use the Data|Filter|AutoFilter option. Your Header Row should be Projectname, etc. The first data row should preferably follow immediately below.

    You can also explore View|Custom Views to use in conjunction with the above. Custom Views enable you to store Filter settings. This way you can quickly switch to all projects Done, In Progress or whatever.

    The data range can also be sorted by Col C Deadline.




    • #3
      Re: Automatically Move &amp; Sort Row Based On Cell Value

      After reading an old post with a similar question, I found a better way to make the project sheet, by using lists in excel for the Status column (now column 5).
      old post

      The difference between our workbooks is that that poster uses an ID for each project in the list, something I don't need, and therefore I think that I can't use that code without modifications (since I think the code in that post adds an ID in the first column after copying).

      Another difference is that I would like to be able to move projects from sheet2 back to sheet1 by changing the Status to Reopened.

      When I used the code from that post, the first part (moving the row from sheet1 to sheet2) went fine but when I changed the Status to Reopened, to move it back to sheet1, I got into an endless loop which crashed the program.

      Also, I'm still not sure where to add the sort sub. I would really appreciate some expert help Thanks in advance.

      I'm adding a copy of my workbook to this post.
      Attached Files


      • #4
        Re: Automatically Move &amp; Sort Row Based On Cell Value

        Other than the fact that you simply *want* to move projects back and forth between sheet1/sheet2 based on project status, there doesn't seem to be any obvious or compelling logic/need to do it.

        I would really appreciate some expert help.
        Auto-Filter and/or Custom Views is, IMHO, the right direction for this, as Sesises has already pointed out. You could create macros to automate applying the auto-filter or custom view and assign each to a button from the Forms Toolbar. Then, to call the desired view, click the applicable button.

        Another option: hide rows that have a project status of "done" and unhide them when you need to reactivate a project. Hiding the rows can also be automated via the worksheet selection change event.

        I'm still not sure where to add the sort sub.
        How often and when do want to perform the sort? The code for sorting could be added to each of the above buttons (if you choose that option) so that the specific view is sorted when called. It could also be placed in the worksheet_activate event so that the data sorts when the sheet is made active.

        Forum Rules | Message to Cross Posters | How to use Tags


        • #5
          Re: Automatically Move &amp; Sort Row Based On Cell Value

          Thanks a lot both of you! Autofilters and buttons to run macros to change them was a great solution. I was able to create a simple enough form with enough functionality.