excel - 复制行，将新工作表粘贴为包含数据的最后一行，然后从源工作表中删除行

Question

下面的代码是根据列“Y”中的“是”从一张表中复制一行，然后粘贴到完成的表中。然后它应该从源选项卡中删除前一行。这项工作第一次完美，但现在已经停止。第二次使用时，它会删除“已完成”选项卡上的数据，然后还会删除源选项卡“项目注册”上的数据。请有人可以帮助我。

Sub CopyClosedProjects()
    Dim wsSource As Worksheet
    Dim wsDestin As Worksheet
    Dim lngDestinRow As Long
    Dim rngSource As Range
    Dim rngCel As Range

    Set wsSource = Sheets("Service Transition Register")
    Set wsDestin = Sheets("Completed Projects")

    With wsSource
        Set rngSource = .Range(.Cells(3, "Y"), .Cells(.Rows.Count, "Y").End(xlUp))
    End With

    For Each rngCel In rngSource
        If rngCel.Value = "Yes" Then
            With wsDestin
                lngDestinRow = .Cells(.Rows.Count, "Y").End(xlUp).Offset(1, 0).Row
                rngCel.EntireRow.Copy Destination:=wsDestin.Cells(lngDestinRow, "A")
            End With
        End If
    Next rngCel


    With wsSource

        Dim rng As Range
        Set rng = ActiveSheet.UsedRange

        For i = rng.Cells.Count To 1 Step -1
            If rng.Item(i).Value = "Yes" Then
               rng.Item(i).EntireRow.Delete
            End If
        Next i
    End With

End Sub

标签： excelvba