Auto-Archive deleted Rows in Excel

[liljunga]

Hello, I'm trying to create a VBA script in Excel that moves deleted rows to an 'Archive' sheet when users right-click and delete entire rows. I've tried the following code, but it's not working as expected. When I right-click and delete rows, they are not moving to the 'Archive' sheet. Could someone please help me troubleshoot this issue? Or can someone provide me new code to test out?

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object, ByVal Target As Range)
Dim wsArchive As Worksheet
Dim lastRow As Long

' Name of your Archive sheet
Set wsArchive = ThisWorkbook.Sheets("Archive")

' Determine the last row in the archive sheet
lastRow = wsArchive.Cells(wsArchive.Rows.Count, 1).End(xlUp).Row

' Copy the deleted rows to the archive sheet
Target.EntireRow.Copy wsArchive.Rows(lastRow + 1)

' Clear the deleted rows from the original sheet
Application.EnableEvents = False
Target.EntireRow.Delete
Application.EnableEvents = True
End Sub

 

[liljunga]

As we spoke before, it doesn't know when somebody intends to insert or delete a row.

Create a macro button. Developer > Insert > ActiveX Command Button. Draw it on the screen. Right click and choose View Code. Add "ArchiveRow" in between the 2 lines.

Wonderful! Thanks for all the help Jeffrey!