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]

The following macro assumes you have headers in row 1 and the data starts in row 2 with no blank rows or columns. Change the column number (in red) to match the column containing "Completed" and the sheet names (in blue) to suit your needs.

Sub CopyData()
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        .Cells(1, 1).AutoFilter 16, "Completed"
        .AutoFilter.Range.Offset(1).Copy Sheets("Closed").Cells(Sheets("Closed").Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[LAMB]

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

Great question, I would like to know the same.

 

[mumps]

See Post #21.