Autodelete a row from protected sheet and move to another protected sheet based on date

[SaraWitch]

Hello peeps,

In a protected sheet ("Placements"), I have a formula in column U that calculates a date from a range of other cells. When a date is populated, I want the row to automatically delete and paste into another protected sheet ("Leavers"). I think I'm almost there with the formula below but know I have to change the <> in line 4 (I've tried some date text but it doesn't work).

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect ("password")
    Dim NextRow As Long
    If Target.Column <> 21 Then Exit Sub
    Application.EnableEvents = False
    With Sheets("Leavers")
        NextRow = .Cells(Rows.Count, 8).End(xlUp).Row + 1
        Target.EntireRow.Copy Destination:=.Cells(NextRow, 1)
        Target.EntireRow.Delete
    End With
    Application.EnableEvents = True
ActiveSheet.Protect ("password")
End Sub

Any help would be gratefully received

 

[MARK858]

Can you upload a copy of your file to a free file hosting site like www.box.com/personal or www.dropbox.com, mark the file for sharing and post the link it provides in your original thread.

Before uploading
1) make sure you sanitize any sensitive data
2) change the passwords on the sheets to something different
3) post the new sheet passwords in the thread so that we can test

Btw, I ran the code that you posted in post number 10 on some sample data with Left in various cells in column G via formula (on unprotected sheets obviously), and it copied the data to the sheet Exceeded fine

 

[Fluff]

In future please do not "hijack" other peoples threads with your own question. Especially when it duplicates this thread.
I have moved the replies over.

 

[SaraWitch]

Thank you, @Fluff. I thought linking the two threads would be sufficient, so, apologies, and thank you for moving the replies over.

@MARK858, here is a link to the workbook: EA Placement Manager.xlsm. Any help you can offer would be gratefully received...

 

[SaraWitch]

Thank you, @Fluff. I thought linking the two threads would be sufficient, so, apologies, and thank you for moving the replies over.

@MARK858, here is a link to the workbook: EA Placement Manager.xlsm. Any help you can offer would be gratefully received...

Happy new year, @MARK858!

I wonder if you have had a chance to have a look at this? I'm really not sure why it's not working?

 

[MARK858]

Sub Move_Row_to_Leavers_Sheet()
    Dim lr As Long, i As Long
    Worksheets("EBC EA Leavers").Unprotect
    
    For i = Sheets("EBC EA Placements").Columns(3).Find("*", , xlValues, , xlByRows, xlPrevious).Row To 8 Step -1
        
        If Sheets("EBC EA Placements").Cells(i, 22).Value = "Left" Then
            
            
            With Worksheets("EBC EA Leavers")
                lr = .Columns(3).Find("*", , xlValues, , xlByRows, xlPrevious).Row
                
                If lr < 8 Then
                    Sheets("EBC EA Placements").Cells(i, 22).EntireRow.Copy .Cells(8, 1)
                    Sheets("EBC EA Placements").Cells(i, 22).EntireRow.Delete
                Else
                    Sheets("EBC EA Placements").Cells(i, 22).EntireRow.Copy .Cells(lr + 1, 1)
                    Sheets("EBC EA Placements").Cells(i, 22).EntireRow.Delete
                End If
               
            End With
       
       End If
    
    Next
End Sub

or use the autofilter

 

[SaraWitch]

Thank you, @MARK858. This just doesn't work for me, but I really do appreciate your time on this

 

[MARK858]

Strange, it works for me with the file you uploaded

Look at the "EBC EA Leavers" sheet
Then go to the "EBC EA Placements" sheet and click the button that I have put there
Then look at the result in the "EBC EA Leavers" sheet

Edit: file deleted at OP's request

 

[SaraWitch]

Ahhhh! Perfect (I wasn't assigning as a macro! )! I just need it to work with password protection on both sheets - is this possible? And is it further possible to re-protect the sheets with the same password the row/s have moved?

 

[mixeycray]

I shall reboot and try with a smaller section, if that would help...?

 

[Joe4]

I shall reboot and try with a smaller section, if that would help...?

That is a very odd post for your first post when you weren't the one to post the original question...
Not really sure if you are trying to help or asking for help.