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

 

[Joe4]

Too tiny for me to read, and there doesn't appear to be any way for me to zoom in on it.

Let's go about this in a different way. So column U has a formula that returns dates, correct?
And when you make updates to other cells, the values in column U get updated, correct?

What kind of dates get put in column U? Today's dates? Past dates? Future dates?
And what are the criteria on the dates in column U and when the row gets moved to the other sheet?
Is it any date in column U, or just certain ones that get moved?

 

[SaraWitch]

Yes, column U has a formula that returns dates.
Yes to second point too.

The date in column U populates with whatever date is last entered in other columns (AF, AS, CS, etc.), and would always be today or future dates (more likely future dates).
I want to move the row when the populated date in column U is past today's date.
I want the whole row to move from one protected sheet to another protected sheet once column U shows a date.

I hope I've understood your questions correctly - shout if not...

 

[SaraWitch]

*column U has a formula that returns the last end date

 

[Joe4]

OK, this may not be the most efficient, but see if this does what you need. Anytime ANY cell is re-calculated on the sheet, it will check ALL of column U, and move any future dates it finds to the other sheet.
Does that work?

Private Sub Worksheet_Calculate()
    
    Dim lr As Long
    Dim r As Long
    Dim NextRow As Long
    
    ActiveSheet.Unprotect ("password")
    
    Application.ScreenUpdating = False

'   Find last row with data in column U
    lr = Cells(Rows.Count, "U").End(xlUp).Row

'   Loop through all rows in column U starting with row 8
    For r = 8 To lr
'       See if value in column U is a future date
        If IsDate(Cells(r, "U")) Then
            If Cells(r, "U") > Date Then
'               Move row to other sheet & delete from original
                Application.EnableEvents = False
                With Sheets("Leavers")
                    NextRow = .Cells(Rows.Count, 8).End(xlUp).Row + 1
                    Cells(r, "U").EntireRow.Copy Destination:=.Cells(NextRow, 1)
                    Cells(r, "U").EntireRow.Delete
                End With
                Application.EnableEvents = True
            End If
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    ActiveSheet.Protect ("password")
    
End Sub

 

[SaraWitch]

Sorry for the delay - something came up.

Thank you for the code, @Joe4 - however, it's returning an error on line, "Cells(r, "U").EntireRow.Copy Destination:=.Cells(NextRow, 1)". I would also need the row to move when the date in U is past today's date, not future...

 

[Joe4]

I would also need the row to move when the date in U is past today's date, not future...

Then change this line:

If Cells(r, "U") > Date Then

to this:

If Cells(r, "U") < Date Then

 

[SaraWitch]

How embarrassing! Of course; thank you.

I am still getting the error though - at the point of entering a date in any other sheet cell (i.e., the dates on which the formula in column U is based): "Cells(r, "U").EntireRow.Copy Destination:=.Cells(NextRow, 1)"

 

[Joe4]

What exactly are you entering in and where (sheet, column, row references and value)?
What is the exact error message you are getting?

 

[SaraWitch]

Sorry, @Joe4, this just isn't working for me.

I did figure out why I was getting the error message - it was because the 'Leavers' sheet into which the row was moving is also protected. I couldn't work out how to unprotect that sheet whilst performing the action within the code, so I unprotected to test but the code is taking a very long time to run, and then the rows are deleting from the original sheet, but disappearing completely - they are not on the 'Leavers' sheet...?

 

[SaraWitch]

I've been thinking how this could be done differently, so, I've added a column (column V) after column U (where the formula calculates a date) and have put a formula in column V to return "Due" or "Left" depending on the date in column U (whether it is after or before today's date). Could a code then move the row from one protected sheet to another protected sheet based on the text in column V being "Left"?