0

The below code is to copy a row from one sheet based on column 'Y' having "Yes" in it and then pasting to the completed sheet. It should then delete the previous row from the source tab. This work perfectly the first time but has now stopped. When used a second time it deletes the data on 'completed' tab and then also on the source tab 'Project Register'. Please can someone help me. 

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
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
Hsmith
  • 7
  • 4
  • 1
    `Set rng = ActiveSheet.UsedRange` should be `Set rng = .UsedRange` – Scott Craner Mar 26 '20 at 16:16
  • 2
    I would change the approach here. Create a union of your target cells (cells to copy/delete). Then you can copy the entire union at once and delete the entire union at once. Will def help to boost the run time of your macro – urdearboy Mar 26 '20 at 16:19
  • 1
    Or even better, just filter on `yes` and work with the visible cells. (This requires no loop) – urdearboy Mar 26 '20 at 16:29
  • It is advisable to avoid using `UsedRange`. Find the last row as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) and then construct your range. Also using autofilter is a good idea. This way you will not have to loop. [HERE](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) is one example – Siddharth Rout Mar 26 '20 at 16:49
  • 1
    @urdearboy, the OP is basically asking the same question again, I provided an [answer using Autofilter](https://stackoverflow.com/questions/60850693/copyiny-a-row-to-a-new-sheet-when-completed-based-on-yes-being-in-column-y) and he marked it as accepted. – GMalc Mar 26 '20 at 17:30

0 Answers0