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 you are working with protected sheets, you will need to first unprotect them in order to make any edits to them. You can do that at the beginning of your VBA code.
See here: https://www.wallstreetmojo.com/vba-unprotect-sheet/

You can then re-protect the code at the end of your VBA code after the changes have been made.

 

[felixstraube]

If the text is "x" then try this:

Libro1
	C	D	E	F	G	H	I	J	K	L	M
3	Date	Name	Department	Where Purchased	Hi Visibility Shirt	Quantity Remaining	Long Trousers	Quantity Remaining2	5 in 1 Jacket	Soft Shell	Quantity Remaining3
4	03/07/2023	Name01	Depot	The Uniform Zone	5	x	5		1	1
5	03/07/2023	Name02	Depot	The Uniform Zone		5		5	1		x
6	04/07/2023	Name03	Depot	The Uniform Zone	5	x	2	3			2
7	04/07/2023	Name03	Depot	The Uniform Zone	5	x	5				2
8	05/07/2023	Name04	Depot	The Uniform Zone		5	2	3			2
9	15/07/2023	Name05	Depot	The Uniform Zone		5	5				1
10
11
12
13
14	Date	Name	Department	Where Purchased	Hi Visibility Shirt	Quantity Remaining	Long Trousers	Quantity Remaining2	5 in 1 Jacket	Soft Shell	Quantity Remaining3
15	03/07/2023	Name01	Depot	The Uniform Zone	5	x	5	0	1	1	0
16	03/07/2023	Name02	Depot	The Uniform Zone	0	5	0	5	1	0	x
17	04/07/2023	Name03	Depot	The Uniform Zone	5	x	2	3	0	0	2
18	04/07/2023	Name03	Depot	The Uniform Zone	5	x	5	0	0	0	2
Hoja1

Cell Formulas
Range	Formula
C14:M18	C14	=LET(t, "x", IFERROR(VSTACK(Hoja1!$C$3:$M$3,FILTER(Hoja1!$C$4:$M$9,(Hoja1!$H$4:$H$9=t)+(Hoja1!$J$4:$J$9=t)+(Hoja1!$M$4:$M$9=t))),C3:M3) )
Dynamic array formulas.

 

[SaraWitch]

Thank you, and what would the workbook VBA that @MARK858 gave look like instead of 0:

Sub Filterit()
    Application.ScreenUpdating = False
  
    With Sheets("Current").Range("G2:G" & Sheets("Current").Range("G" & Rows.Count).End(xlUp).Row)
  
        .AutoFilter 1, 0
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Copy _
        Sheets("Exceeded").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter
      
    End With
     
    Application.ScreenUpdating = True
End Sub

I have tried "Left" which will be my trigger word but this isn't working:

Sub Filterit()
    Application.ScreenUpdating = False
  
    With Sheets("Current").Range("G2:G" & Sheets("Current").Range("G" & Rows.Count).End(xlUp).Row)
  
        .AutoFilter 1, "Left"
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Copy _
        Sheets("Exceeded").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter
      
    End With
     
    Application.ScreenUpdating = True
End Sub

 

[SaraWitch]

Thank you, @Joe4.

I have tried several VBAs but am getting nothing to work.

If okay, I may post on another forum... Is this okay with the rules?

 

[MARK858]

Is Left the only word in the cell?

 

[Joe4]

Thank you, @Joe4.

I have tried several VBAs but am getting nothing to work.

If okay, I may post on another forum... Is this okay with the rules?

It is fine, as long as you mention you are doing so and post a link to the other posts, as outlined in rule 13 here: Message Board Rules

 

[SaraWitch]

Yes - "Left" and "Due" are returning based on a formula calculating from previous cell's date. And I need to move the row from one protected sheet to another protected one if the formula value is "Left".

Just for transparency I have already posted my query under the heading, "Autodelete a row from protected sheet and move to another protected sheet based on date" but have not yet found a solution. I have asked if okay to post on another forum, but before I do, I came across this thread...

 

[MARK858]

If it is only Left in the cell what you done should work if there is no space in front of the Left, you can also try "=Left".
I am not in to test but I'll have a look back when I get in this evening.

As for posting on other forums it is ok as long as you paste a link in the question you asked to the post in the other forum so people don't duplicate their responses.

 

[SaraWitch]

Tried that as well with no joy. I'd really appreciate you taking a look, thank you, @MARK858. Happy to upload the workbook to either here or my original post (Autodelete a row from protected sheet and move to another protected sheet based on date), if that helps?

 

[SaraWitch]

Thank you, @Joe4.

Just to let everyone know, before posting on another forum, I did find another thread (Automatically Move Row to Another Sheet Based On Cell Value) and @MARK858 is very kindly looking at this for me.