Sorting data after moving a row in from another sheet

[thegillimonster]

Hello!

Disclaimer: Extremely new to VBA. Not computer-illiterate, but not familiar with VBA.

I have a table with action items containing a column which marks each item as Open or Complete. My end-state is to move rows in the "Open" sheet into the "Complete" sheet as soon as the action item is changed from "Open" to "Complete". I derived this code from a post here so thank you to wherever I got that from, it works great .

After the copy and paste, I want the code to then sort the pasted data so that the action item # continues to line up. For example, if items 1,2,3 are marked complete in the order 2,1,3, they will appear in the "Complete" sheet in the order they were marked. I want the second step of my code to then sort the data so that the items in the "Complete" sheet follow the correct 1,2,3 order. This section of my code was done using a macro, but it fails when I try to shoehorn it into my old code. The glaring error that I see is .SetRange Range("A2:G11"), because that range will change each time a new row is added and the code does not account for this.

My code as it exists currently looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Version 2
'Move Action item from Open list to Complete list

'Set target row in Complete sheet by finding last row and adding 1 
If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Complete").Cells(Rows.Count, "F").End(xlUp).Row + 1

'Copy and paste newly marked Complete row from Open sheet into Complete sheet
If Target.Value = "Complete" Then
Rows(Target.Row).Copy Destination:=Sheets("Complete").Rows(Lastrow)
Rows(Target.Row).Delete

'Macro-derived formula to select and sort proper row in Complete sheet. This part is not working.
    Sheets("Complete").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Complete").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Complete").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Complete").Sort
        .SetRange Range("A2:G11")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If
End If
End Sub

Data in the "Open" sheet looks like this:

Title
#	Action	Topic	Actor	Deadline	Status	Comments
1	Steve to do X	BBQ	Steve	17 May 2019	Open	Steve needs Y
2	Evan to buy Y	BBQ	Evan	17 May 2019	Open	Evan needs funds
3	Carl to organize baseball	BBall	Carl	24 May 2019	Open	Need attendance list

<tbody>
</tbody>

 

[thegillimonster]

Error given is

Runtime error 1004
Select method of Range class failed

 

[thegillimonster]

I have a very unelegant solution. It works, but I don't like how inefficient it is. On the "Complete" sheet, I added code to sort the data each time the sheet is clicked on.

It bothers me that I couldn't really figure it out though. How do you run code in Sheet1 that affects Sheet2? I think that is the crux of my problem.