VBA code to automatically archive updated data only

[irelanduser22]

Good morning,

I'm new to VBA and have experimented with a couple of codes but not quite got to what I want to achieve.

I have a progress tracker worksheet "Tracker" where multiple users will update on their respective tasks. Once complete, they will add the date of the progress update in Column T. Each time the date in Column T is amended, I want a copy of the data in that row only to be sent to a separate "Archive" sheet.

I've experimented with a Private Sub Worksheet_Change(ByVal Target As Range) code similar to the below with some success, but only by including a "Yes/No" update complete field in Column U. In addition, this code copies all rows each time any row is updated, rather than just the updated row.

Any help welcome,

Thanks.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 20 Then Exit Sub
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    If IsDate(Target) Then
        Target.EntireRow.Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[irelanduser22]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 20 Then Exit Sub
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    If IsDate(Target) Then
        Target.EntireRow.Copy Sheets("Archive").Cells(Sheets("Archive").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

That's perfect, exactly what I was after - just removed the Target.EntireRow.Delete line so the latest update is always visible in the main tracker. Thanks very much.

One follow up query (apologies - probably a simple one), I'd like to password protect the "Archive" sheet so only authorised users can delete or edit the information. However, I want the above code to run regardless - so data can be copied into the archive by anyone, but only deleted form the archive by authorised users (who have the password).

Normal password protect prevents the code from running.

 

[mumps]

Change the password (in red) to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 20 Then Exit Sub
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    If IsDate(Target) Then
        With Sheets("Archive")
            .Unprotect Password:="MyPassword"
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            .Protect Password:="MyPassword"
        End With
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[irelanduser22]

Great stuff, that's ideal. Thanks for your help!

 

[mumps]

My pleasure.