Transfer Information From One Sheet To Another Based On Criteria

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

Try this

Code:

---


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.

Can the code be linked to a ActiveX control
Jeff

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.

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