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