Announcement

Collapse
No announcement yet.

move rows to another worksheet based on a cell value in that row?

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • move rows to another worksheet based on a cell value in that row?



    Hello,

    I currently have a consolidated worksheet (thanks Bill!) called "Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up?

    Edit: here are some additional information:

    The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)

    The "status" column is at column 11.


    Thanks for your help in advance,
    Henry

  • #2
    Re: move rows to another worksheet based on a cell value in that row?

    Bioture,

    This code should do the trick. It requires that the status column have an autofilter. It then filters for closed, copy the cells to completed and then deletes the extra rows in Dashboard.
    Code:
    Sub MoveClosed()
        Sheets("Dashboard").Select
        Selection.AutoFilter Field:=1, Criteria1:="Closed"
        Range("A7").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Completed").Select
        Range("A1").Select
    '   The next 3 steps require that there be at least two rows of data in Completed
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveSheet.Paste
        Sheets("Dashboard").Select
        Application.CutCopyMode = False
        Selection.EntireRow.Delete
        Selection.AutoFilter Field:=1
        ActiveCell.Select
    End Sub
    It's worth mentioning that cutting instead of copying and deleting provides bad results. The attached file has a working sample.

    Steve
    Attached Files

    Comment


    • #3
      Re: move rows to another worksheet based on a cell value in that row?

      Hi Steve,

      Thanks for the snippet of code, it copies and pastes the data on the completed sheet just like it should! There are two problems i'm experiencing:

      1) The locked 6 row header for the "dashboard" worksheet is the same in "complete". When I run the macro for the first time, all the data gets copy/pasted to row 4 and on, on the completed worksheet. Also, when it does this, it pastes over completed records that were previously there.

      2) The macro only works once - afterwards if i change more rows to have a "closed" status and run the macro again, the row of data is copy and appended to end of the list, however only 7 colums are copied (there are 13 colums in total for the worksheets).

      Edit: I fixed #1 by fiddling with the snippet and changed
      Range("A7").Select
      on completed. however, #2 is still a problem. I can't run the macro twice without this problem occurring.


      Give me some more help?
      Thanks for your patience,
      Henry

      Comment


      • #4
        Re: move rows to another worksheet based on a cell value in that row?

        Hi Henry,

        Easy fixes. The original macro was based on the completed tab starting in row 1. I suspect that row 7 in column 8 was empty. Try this:
        Code:
        Sub MoveClosed()
            Sheets("Dashboard").Select
            Selection.AutoFilter Field:=1, Criteria1:="Closed"
            Range("A7").Select
            Range(Selection, Selection.End(xlDown)).Select
        '   Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, ActiveCell.Offset(0, 13).Range("A1")).Select
            Selection.Copy
            Sheets("Completed").Select
            Range("A7").Select
        '   The next 3 steps require that there be at least two rows of data in Completed
            Selection.End(xlDown).Select
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveSheet.Paste
            Sheets("Dashboard").Select
            Application.CutCopyMode = False
            Selection.EntireRow.Delete
            Selection.AutoFilter Field:=1
            ActiveCell.Select
        End Sub
        Steve

        Comment


        • #5
          Re: move rows to another worksheet based on a cell value in that row?

          works awesome! thanks!

          Comment


          • #6
            Re: move rows to another worksheet based on a cell value in that row?

            Wery nice. Nearly what Im looking for.
            Can You help me change so it will move less than two rows and do it automatic every time the workbook opens?

            Comment


            • #7
              Re: move rows to another worksheet based on a cell value in that row?

              Anderssk

              Can you start a new thread with details of your problem?
              Boo!

              Comment


              • #8
                Re: move rows to another worksheet based on a cell value in that row?

                I am trying to get the same thing Henry was asking to happen in my workbook and then some.

                I have a wkbook with 3 wksheets (Tracking, Closed, Hist)

                When an item is marked "closed" I would like the row deleted from Tracking and pasted to Closed. Note - Closed is selected from a drop down menu.

                I have tried to use the above mentioned code to make this happen, changing Dashboard to Tracking and Closed to Completed - however this obviously was not the answer.

                now my "and then some"

                When I enter an item into Tracking for the first time I would like it to automatically duplicate on the Hist sheet. With the exception, my tracking sheet shows me the current location and date and I would like Hist to keep a history of these dates and locations, i.e. each time I change "joe's" location and date in Tracking, I would like "joe's" location and date on the Hist sheet to keep adding to the first available cells on his row.

                A million thanks for any help you can provide.

                BJ

                Comment


                • #9


                  Re: move rows to another worksheet based on a cell value in that row?

                  Did you see my comment?

                  Start a new thread, this one was started almost 2 years ago and the last post to it was made a year ago.
                  Boo!

                  Comment

                  Working...
                  X