Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

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

  1. #1
    Join Date
    12th July 2005
    Posts
    14

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th February 2005
    Location
    The Wrong Coast
    Posts
    130

    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.
    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th July 2005
    Posts
    14

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th February 2005
    Location
    The Wrong Coast
    Posts
    130

    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:
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th July 2005
    Posts
    14

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

    works awesome! thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    3rd March 2006
    Posts
    5

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    14th July 2004
    Posts
    10,539

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    4th March 2007
    Posts
    2

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    14th July 2004
    Posts
    10,539

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Move Rows In Table Based On Cell Values
    By scsishere in forum EXCEL HELP
    Replies: 6
    Last Post: May 2nd, 2008, 11:35
  2. Move Rows To Worksheets Based On Cell Text In Row
    By geeky_15601 in forum EXCEL HELP
    Replies: 6
    Last Post: March 19th, 2008, 10:57
  3. Replies: 7
    Last Post: January 17th, 2008, 23:31
  4. Move Row To Another Worksheet Based On A Cell Value
    By world_trvler in forum EXCEL HELP
    Replies: 1
    Last Post: March 4th, 2007, 08:23
  5. Move rows to another worksheet based on a cell value
    By uwanmebabe in forum EXCEL HELP
    Replies: 2
    Last Post: July 19th, 2005, 21:01

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
  •  
porno