VBA - copy data onto new sheet, and remove it

[Nikhil R]

So I've been tinkering with the Filter & Choosecols filter as I've created a sheet called Rejected Candidates. It's like for like as shown on the Interested Candidates sheet.

I've got it so it copies everything onto rejected candidates sheet IF rejected is shown as yes on interested candidates - but i need it to remove that row.

so essentially:
1) cut the whole row from "interested candidates sheet" IF "Rejected" is set to Yes.
2) Once Yes - copied onto the "Rejected candidates" sheet
3) Once that's done - going back to the "Interested candidates" sheet to then move the other rows up into the empty space above once the row has been deleted.

I did have another formula which was =FILTER but it did the same thing as the one below but now realise VBA is what I'll need use which i'm extremely rusty on. Last time I used it - I managed to create tic-tac-toe and memory lol.

=CHOOSECOLS(FILTER(Interested_Candidates,Interested_Candidates[Rejected]="yes"),MATCH(A5:M5,Interested_Candidates[#Headers],0))

 

[Nikhil R]

Even with the above formula - trying to use VBA to remove the row that has yes on the "rejected" column on candidate interested sheet as it's transferred over into the rejected candidate sheet is difficult. unless there's a full vba code to cut the row that has a rejected candidate into the rejected sheet and then removed on the candidate interested sheet

 

[LazyBug]

Sub nikhil_r()
Dim l&, i&
Application.ScreenUpdating = 0
    With Worksheets("Interested Candidates")
        l = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = l To 4 Step -1
            If .Cells(i, 12).Value = "Yes" Then
                .Rows(i).Copy
                Sheets("Rejected Candidates").Range("A" & Sheets("Rejected Candidates").Cells(Sheets("Rejected Candidates").Rows.Count, 1).End(xlUp).Row + 1)
                .Rows(i).Delete
            End If
        Next i
    End With
Application.ScreenUpdating = 1
End Sub

 

[Nikhil R]

Sub nikhil_r()
Dim l&, i&
Application.ScreenUpdating = 0
    With Worksheets("Interested Candidates")
        l = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = l To 4 Step -1
            If .Cells(i, 12).Value = "Yes" Then
                .Rows(i).Copy
                Sheets("Rejected Candidates").Range("A" & Sheets("Rejected Candidates").Cells(Sheets("Rejected Candidates").Rows.Count, 1).End(xlUp).Row + 1)
                .Rows(i).Delete
            End If
        Next i
    End With
Application.ScreenUpdating = 1
End Sub

Thank you! It does come up with this error which is:

Runtime 438
object doesn't support this property or method

 

[LazyBug]

It does come up with this error

Please try:

Sub nikhil_r_02()
Dim l&, i&
Application.ScreenUpdating = 0
    With Worksheets("Interested Candidates")
        l = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = l To 4 Step -1
            If .Cells(i, 12).Value = "Yes" Then
                .Rows(i).Copy Sheets("Rejected Candidates").Range("A" & Sheets("Rejected Candidates").Cells(Sheets("Rejected Candidates").Rows.Count, 1).End(xlUp).Row + 1)
                .Rows(i).Delete
            End If
        Next i
    End With
Application.ScreenUpdating = 1
End Sub

 

[Nikhil R]

Please try:

Sub nikhil_r_02()
Dim l&, i&
Application.ScreenUpdating = 0
    With Worksheets("Interested Candidates")
        l = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = l To 4 Step -1
            If .Cells(i, 12).Value = "Yes" Then
                .Rows(i).Copy Sheets("Rejected Candidates").Range("A" & Sheets("Rejected Candidates").Cells(Sheets("Rejected Candidates").Rows.Count, 1).End(xlUp).Row + 1)
                .Rows(i).Delete
            End If
        Next i
    End With
Application.ScreenUpdating = 1
End Sub

Amazing - I missed out a couple things when i did mine coding and compared to this and just tweaked it - thank you so much

 

[LazyBug]

and just tweaked it

Glad I could help & thanks for the feedback.