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

 

[SaraWitch]

Huge apologies for the delay in responding (I've been in India for a month and it was a bit hectic just before leaving!). Upon my return, the VBA seems to be working perfectly again! Blessed goblins!

 

[SaraWitch]

Hello @MARK858.

I hope this finds you well and dandy.

I'm trying to tweak this formula for another workbook to move a row based on a column containing the words "Let", "Cancelled", or "Complete". I've tried a couple of variations for the 7th line but am tying myself up in knots. Any help would be appreciated, thank you

 

[MARK858]

One way...

If Sheets("EBC EA Placements").Cells(i, 22).Value = "Let" Or Sheets("EBC EA Placements").Cells(i, 22).Value = "Cancelled" Or Sheets("EBC EA Placements").Cells(i, 22).Value = "Complete" Then

 

[SaraWitch]

Amazing! Thank so much, Mark

 

[SaraWitch]

Me again!

I'm now trying to password protect the button to which the macro is assigned. I found a code that @Fluff had provided in Is it possible to password protect a button? (I didn't want to hijack that thread!) and have tried to put it into my code, but it's not working. I'm sure I'm missing something obvious, but any help would be appreciated:

Sub Move_Row_to_Completed_Voids_Sheet()

    Dim Ans As Variant
    Const Pword As String = "HFA"
    
    Do While Ans <> Pword
        Ans = Appication.InputBox("Please enter password to continue.", "Enter Password")
        If Ans = Pword Then Exit Do
        If Ans = False Then Exit Do
    Loop
    
Dim lr As Long, i As Long

Application.ScreenUpdating = False

  
Worksheets("Completed Voids (EBC)").Unprotect "HFL"
Worksheets("Current Voids (EBC)").Unprotect "HFL"

    For i = Sheets("Current Voids (EBC)").Columns(4).Find("*", , xlValues, , xlByRows, xlPrevious).Row To 8 Step -1
     
        If Sheets("Current Voids (EBC)").Cells(i, 2).Value = "Cancelled" Or Sheets("Current Voids (EBC)").Cells(i, 2).Value = "Complete" Then '(i, then column number (currently C2, C.B))
         
         
            With Worksheets("Completed Voids (EBC)")
                lr = .Columns(4).Find("*", , xlValues, , xlByRows, xlPrevious).Row
             
                If lr < 8 Then
                    Sheets("Current Voids (EBC)").Cells(i, 2).EntireRow.Copy
                    .Cells(8, 1).PasteSpecial xlValues
                    Sheets("Current Voids (EBC)").Cells(i, 2).EntireRow.Delete
                Else
                    Sheets("Current Voids (EBC)").Cells(i, 2).EntireRow.Copy
                    .Cells(lr + 1, 1).PasteSpecial xlValues
                    Sheets("Current Voids (EBC)").Cells(i, 2).EntireRow.Delete
                End If
            
               Application.CutCopyMode = False
             
            End With
    
       End If

    Next


Worksheets("Completed Voids (EBC)").Protect "HFL", AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
Worksheets("Current Voids (EBC)").Protect "HFL", AllowFiltering:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

Application.ScreenUpdating = True

End Sub

 

[SaraWitch]

Ah! Just found a solution from @MattH1 in the thread Password Protect A Macro Button which works perfectly! (Thank you, @MattH1!)

Dim password As Variant
              password = Application.InputBox("Enter Password", "Password Protected")

Select Case password
                  Case Is = False
                      'do nothing
                  Case Is = "PASSWORD" 'Edit this line with the password you wish to protect your macro with.
                      'This is where your code will go.
                  Case Else
                      MsgBox "Incorrect Password"
End Select