Need help moving row to second worksheet

[dwoychowski]

I am brand new to macros and tried using the record function, but can't get it to work properly.

I am looking to have every row with the status "Done" (column E) moved to the "Completed" tab and the remaining blank line to be deleted.

My task list starts on row 3 and goes from column B to column H.

Any help would be greatly appreciated!

 

[Joe4]

Welcome to the Board!

Where are you pasting the data to on the Completed tab?
Will it always be on row 1 or 2, or might there already existing data on your Completed tab, and you need to paste the new data below that?
Are you not wanting to copy/paste column A?

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Please()
'Modified  2/18/2020  11:13:39 AM  EST
Application.ScreenUpdating = False
Dim lastrow As Long
Dim c As Long
Dim s As Variant
c = 5 ' Column Number Modify this to your need
s = "Done" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Completed").Rows(1)
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[dwoychowski]

Welcome to the Board!

Where are you pasting the data to on the Completed tab?
Will it always be on row 1 or 2, or might there already existing data on your Completed tab, and you need to paste the new data below that?

Thanks for the Reply! I would like the data to be pasted on the completed tab starting on row 3 (same columns B:H), but continuing on the next row for each new line added. I'm sorry I wasn't clear before.

 

[Joe4]

OK, before I take a run at it, try out the code provided by "My Aswer is This" and let us know if that seems to work for you.

 

[My Aswer Is This]

I was not totally clear either about what you wanted.
My script copies the entire row to sheet Completed
But my script copies to row(1) and below.
Change Rows(1) to 3

 

[dwoychowski]

I was not totally clear either about what you wanted.
My script copies the entire row to sheet Completed
But my script copies to row(1) and below.
Change Rows(1) to 3

Thanks for your help! It worked for most of the rows, but there were two rows with the status "Done" that were not moved to the new sheet. So I tried running the macro again, and get the error code "400" and it also deletes most of the other data in the first worksheet.

 

[Joe4]

Sometimes, it is helpful if you can post a small image of your data, so we can see what it looks like to make sure tha twe have it structured the same way that you do.
There is a little tool that you can use to post images. See here: XL2BB - Excel Range to BBCode

 

[My Aswer Is This]

I test all my scripts and it worked for me.
It looks for the exact value "Done" in column E
If the the value is not exactly "Done" the row will not be copied over.
And you must have a sheet named "Completed"
And the code should be run from the sheet with Done in column E
And my script deletes the entire row if Done is found in column E

 

[CountTepes]

Banged this together. A bit rough and ready.

Private Sub MoveDone()

Dim C As Range
Dim Dest As Range
Dim CopyR As Range

Dim DoneSheet As Sheets
Sheets("Completed").Activate
Do
    Set C = Me.Range("E:E").Find("DONE", LookIn:=xlValues)
    If Not (C Is Nothing) Then
        Me.Range(Cells(C.Row, 2), Cells(C.Row, 8)).Copy
        Set Dest = ActiveSheet.Range("E:E").End(xlDown)
        If Dest.Row = 1048576 Then Set Dest = Dest.End(xlUp)
        Dest.Offset(1, -3).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        C.EntireRow.ClearContents
    End If
Loop Until C Is Nothing
Me.Activate
Me.Range("B:H").Sort key1:=Range("C2"), order1:=xlAscending, Header:=xlYes
 

End Sub