Moving Row to the next available row on a new worksheet

[Infantryman]

Hello,
I have been using a great task tracker that when I enter a complete date, it moves the entire task row to a new worksheet, "Completed Tasks."
Here is the issue I ran into:
When I archive my year of tasks, I save the excel as the following year, then on the current workbook I delete the past completed tasks.
Now when I complete a task, it continues to place them as if there are items above it. How can I make it so the code puts the completed task at the next available row?
Here is the code I am using:

For the Current Tasks:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Completed Work").Range("rngDest")

' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then

' Only trigger if the value entered is a date or is recognizable as a valid date
If IsDate(Target) Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If

End Sub


​

Completed Task Worksheet:

​

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub



​

 

[cooper645]

I am no expert, however,

Looks like rngDest may be a named range in the completed work worksheet. You could sort that issue out or other wise try the following.

Could you amend this line of code

[COLOR=#333333]Set rngDest = Worksheets("Completed Work").Range("rngDest")[/COLOR]

To:

[COLOR=#333333]Set rngDest = Worksheets("Completed Work").Cells(.Rows.Count, "A").End(xlUp).Row
[/COLOR]
rngDest = Cells(rngDest,1)

I can't test it as I'm replying on my phone. Don't forget to test all code changes on a copy of your wookbook first.