Moving row to a "completed" sheet when task is done

[jeannyh]

I know others have asked this question before, but I confess that I know nothing about VBA (except that maybe it stands for Visual Basic?) and have very little experience with macros. If someone could help me with some cut-and-paste code, that would be great.

We have a "Problems" spreadsheet for customers who have issues we're working on. When the task is complete, we'd like to mark "Complete" on the drop-down in Row I and have that row move to a "Completed" sheet. I know there's a way to do it because others have asked this same question and reported success. I don't really understand what to do, though. If someone could treat me like a 67-year old first grader and walk me through the process, I'd really appreciate it.

 

[portews]

Try this:
Put this in the code for the Problems worksheet (see below for more detailed instructions, if needed). Any time there's a change in the sheet it will look in the column for "Completed" and cut & paste that at the bottom of the Completed page and delete the line off the Problems worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Prob As Worksheet
Dim Comp As Worksheet
Dim DelRow As Long
Dim CompLastRow As Long

'just because I'm lazy and don't want to type out the whole word every time.
Set Prob = Sheets("Problems")
Set Comp = Sheets("Completed")

'starting on the Problems worksheet
With Prob
'change the A1:A in the row below to be whatever column has the "Completed" dropdown in it, starting at which row

'if there's a change on the sheet in the column where the Completed's are
    If Not Intersect(Target, .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)) Is Nothing Then
        
        'if that changed cell = "Completed"
        If Target = "Completed" Then
            
            'Stop the program for looking for changes
            Application.EnableEvents = False
            
            'Get the last row in the Completed sheet Change this A to be a column that will always have data in it on the Completed worksheet
            CompLastRow = Comp.Range("A" & Rows.Count).End(xlUp).Row
            
            'Where's the completed row so we can delete it later
            DelRow = Target.Row
            
            'cut the row from the Problems worksheet and paste it at the bottom of the Completed worksheet
            .Range("A" & Target.Row).EntireRow.Cut (Comp.Cells(CompLastRow, 1))
            
            'Delete the empty row on the Problem worksheet
            .Range("A" & DelRow).EntireRow.Delete
            
            'get the program to start looking for changes again
            Application.EnableEvents = True
            
        End If
    
    End If

End With

End Sub

To put the code in your Problems worksheet
Open the developer tab, click on Visual Basic. Something similar to the window below should come up.
Make sure the Project Explorer window on the left is showing. If not click on View and check it off.

In the Project Explorer window, you'll see a listing of your sheets. Double click on the sheet you want to apply this code to, in this case "Problems". This will open up the code page for that sheet. Paste the code above on that page.