Moving row based on value

[rubinda]

I am using the code below. When I activate the macro, the appropriate row disappears, but it is not pasted anywhere.

Sub EdgewoodOpen()
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Edgewood-Open").UsedRange.Rows.Count
    J = Worksheets("Edgewood-Closed").UsedRange.Rows.Count
    If J = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Edgewood-Closed").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Edgewood-Open").Range("E1:E" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "Complete" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Edgewood-Closed").Range("A" & J + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "Complete" Then
                K = K - 1
            End If
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Could you explain in words what you are trying to do using a few examples from your data referring to specific cells, rows, columns and worksheets.

 

[rubinda]

I am trying to copy a row from one spreadsheet (Edgewood-Open) to another (Edgewood-Closed) when the cell in column E states "Complete". Data is disappearing from the "Edgewood-Open" sheet but not transferring to "Edgewood-Closed". Please let me know if you need more info.

 

[Fluff]

What happens if you remove this line

On Error Resume Next

 

[mumps]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Edgewood-Open").Range("E1:E" & LastRow).AutoFilter Field:=1, Criteria1:="Complete"
    Sheets("Edgewood-Open").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Edgewood-Closed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Sheets("Edgewood-Open").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If Sheets("Edgewood-Open").AutoFilterMode = True Then Sheets("Edgewood-Open").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[rubinda]

same thing

 

[Fluff]

Do you get any error messages?

 

[rubinda]

no change. It was working earlier then my computer randomly restarted. When it started back up it didn't work.

 

[rubinda]

no error message

 

[Fluff]

Does mumps code work for you?