Automatically move rows to another sheet and back based on value

[Kgreen214]

Hi,

Currently I am tracking Agenda Items in an excel spreadsheet. I have created the spread sheet with drop down menu’s (See attached Image). Both the Active sheet and the Completed look the same. I’m looking to move a row from my “Active” sheet to the “Completed” sheet once the status column has been updated to Complete. I would also like for that row to be deleted so that there isn’t blank rows on the sheet.

Subsequently I would also like to have it set up that if the status has been updated accidently it can be moved back to the Active sheet just by changing the status back to either “Not Started, In Progress, On Hold or Overdue”

Can someone please assist me with this? Thanks!

 

[mumps]

You are very welcome.

 

[LAMB]

What I just discovered as I am loading up our schedule and showing completed jobs, it over writes A2 in "Closed" every time, is there a fix for this?

 

[mumps]

Can you use the XL2BB add-in (icon in the menu) to post screen shots (not pictures) of both sheets?

 

[LAMB]

I tried to download the XL2BB add in, just goes to a blank excel screen

 

[mumps]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).

 

[LAMB]

Dropbox - Error - Simplify your life

www.dropbox.com

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim LastRow As Long
    If Target = "Completed" Then
        With Sheets("Closed")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            Target.EntireRow.Copy .Range("A" & LastRow)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[LAMB]

That worked great, thank you. If I apply the code to both data/closed, if i move something back to data will it go to bottom of data already on that sheet.

 

[mumps]

It should.

 

[arjunramesh]

I am managing inventory in an excel sheet.
The code you provided works when I paste it in the worksheet module. However, I wanted to use it as a Macro so I can have a button "Update" and should only move the rows when I click the button.

Can someone help me with this?

Thanks