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]

If the value you are watching for changes is a formula and not a hard-coded value, you cannot use a Worksheet_Change event procedure to track.
Worksheet_Change procedure only fire upon MANUAL updates of cells, not changes due to formulas.

There is a Worksheet_Calculate event procedure, but it works a bit differently. It fires whenever any cell on your sheet is re-calculated (i.e. via formula).
However, the big caveat is that unlike Worksheet_Change, Excel cannot tell/identify which cell's value was re-calculated. All it can tell you is that some cell somewhere on the sheet was re-calculated.
So it doesn't sound like that will work for you, in this case.

You may need to come about this a bit differently.
What exactly is the formula in column U?
We may need to watch the cells that you manually update that affect column U instead. But we would need to know more detail about it before we can make that determination.

 

[SaraWitch]

Thank you, @Joe4.

The code in column U is:

=IF(T8="",0,IF(CR8>0,CS10,IF(AR8>0,AS8,IF)SE8>0,AF8))))

What I could do (which may actually work better for reporting purposes because I don't want the row to move if the date is not prior to today), is build into the code to unprotect U8 when a date is showing so the user can physically type the date when the row needs to move...? And that code is stumping me!

 

[Joe4]

That is not even a valid formula.

=IF(T8="",0,IF(CR8>0,CS10,IF(AR8>0,AS8,IF)SE8>0,AF8))))

Here is some advice - don't try to manually type your working formulas or VBA code - use Copy/Paste. That will eliminate any chance of making typos.

It would be helpful to see what this data looks like, and what it might be returning.
Could the value in column U return any date that is NOT the current date, because I do not see anything in your original code checking the value of the date on column U?

I think we need to see/have a better understanding of the data you are working with, and what it might return.

 

[SaraWitch]

This workbook is a bit of a beast, but here it is! I've done a bit extra work on it and there now seems to be a code that keeps returning an error (I am trying to create drop down list but show different values in columns AM, AZ, BM, BZ, CM, CZ - I will log this separately).

EA Placements Manager

Sheet EBC "EA Placements (2)" is my working sheet.

 

[Joe4]

I do not have the ability to download files from my current location.

I've done a bit extra work on it and there now seems to be a code that keeps returning an error

So does that mean the original question you had in this thread has been solved, but you have a different question now?

 

[SaraWitch]

Sorry, @Joe4 - no my original question has not been solved. I'm trying to pull some data out but Xl2bb but it's crashing Excel! I shall reboot and try with a smaller section, if that would help...?

 

[SaraWitch]

I can't create a mini sheet via Xl2bb - it keeps crashing Excel no matter the selected range. Not sure what to do now...?

 

[Joe4]

Can you do screen prints of the key ranges, being sure we can see what column letters they are in?
And walk us through an example?

 

[SaraWitch]

How's this? Columns AA:AW are repeated for a further five placements.