Find certain text in a column, then copy an paste that row to a new sheet.

[schmo95]

Hello all,

I have a spreadsheet with actions/tasks on (basically, column A - Task, B - Priority, C - Status, D - Deadline, E - Due in (Days)) I'm looking for help with a macro that will search column C for the word "Complete", then copy that row to a new sheet which will show completed actions, so the actions list will only have outstanding actions on it. I have set up a UserForm to add tasks, so column C has 3 options (Not started, In Progress, Complete), and the idea is when you change the dropdown box and mark the action as Complete, the row is cut and moved to a separate sheet with completed actions on, and the other cells are shifted up. Any help with this would be greatly appreciated.

Thanks in advance

 

[vcoolio]

Hello Schmo,

Perhaps this may help:-

Sub MoveIt()

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
With Range("C2", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Complete"
        .Offset(1).EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Offset(1).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True
Sheets("Sheet2").Select

End Sub

Attached is my sample test work book for you to peruse:-


https://www.dropbox.com/s/rgmbewyhfwbc87d/Schmo95(Copy by Autofilter).xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

 

[schmo95]

Hi vcoolio,
Is there a better way to do this without auto-filters? This does the job in theory, but ruins the format of my spreadsheet (buttons, charts etc), also I have data validation in places and it doesn't seem to like that. In my head i wanted a formula that would loop round column C, and when it finds the word 'Complete' it would cut the row to another sheet. Because I have a UserForm in place for adding to the table, the filtering is affecting the drop down boxes as they appear in the table, and i think this is because of the filtering?
Thanks for your response the help is appreciated!

 

[vcoolio]

Hello again Schmo,

Replace the above code with the following loop type code:-

Sub MoveIt2()

Application.ScreenUpdating = False
    Dim lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("C3:C" & lRow)
    If cell = "Complete" Then
    cell.EntireRow.Copy
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End If
Next

Application.ScreenUpdating = True
Application.CutCopyMode = False
    
End Sub

I haven't placed any delete lines of code in the above.
You may have to change the sheet and cell references to suit yourself.

See how it goes.

Cheerio,
vcoolio.