Transfer Information From One Sheet To Another Based On Criteria

[jeff]

Greetings,

Please see attached spreadsheet.

On the task sheet I require any Task Descriptions tasks which are marked Completed transferred over to the Archive sheet by utilising the archive button.Once the Completed Task/s has been transferred it is deleted from the Task sheet (row deleted so as no empty rows between tasks)

Best Regards

Jeff

[Excel Fox]

Try this

Sub ArchiveCompleted()

    Dim rng As Range
    
    With Worksheets("TASKS")
        For Each rng In .Range("E5:E" & Application.Max(.Cells(.Rows.Count, "E").End(xlUp).Row, 5))
            If LCase(rng.Value) = "completed" Then
                With Worksheets("ARCHIVE")
                    .Cells(.Rows.Count, "B").End(xlUp)(2).Resize(, 2).Value = Array(rng.Offset(, -3).Value, rng(1, 2).Value)
                    rng.Offset(, -3).Resize(, 5).ClearContents
                End With
            End If
        Next rng
        With .Range("$B$4:$F$" & Application.Max(.Cells(.Rows.Count, "E").End(xlUp).Row, 5))
            .RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        End With
    End With
    
End Sub

Note: This will only work in Excel 2007 or above. If you are using a lower version, post back.

[jeff]

Can the code be linked to a ActiveX control
Jeff

[Excel Fox]

Yes it can. But the control you have on your sheet is not an activex control. You can just right click on that control, and assign macro by selecting the macro name.

[jeff]

Hi

This code is very good but can it remove the empty rows from the task sheet once the data has been transferred to the Archive sheet.This way there is no gaps between tasks in the Task sheet

Best Regards

Jeff