In this VBA code, when I doubleclick on a cell in column H,
it is copying the wrong row from sheet 'Key Allocation' to sheet 'Key Return'

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

ThisWorkbook.Worksheets("Key Allocation").Unprotect password:="edit"
ThisWorkbook.Worksheets("Key Return").Unprotect password:="edit"
ThisWorkbook.Worksheets("Key Audit").Unprotect password:="edit"

Application.EnableEvents = False

  If Target.Address = "$I$1" Then
   Cancel = True  ' Prevent edit mode for I1
    ' Clear specific ranges
    Range("A2:H2").ClearContents
    Range("I3").ClearContents
    Range("I4").ClearContents
    Range("A4:H300").ClearContents
    MsgBox "Page has been reset"
  End If
  
If Target.Column <> 8 Or Target.row < 4 Then Exit Sub ' Check if double clicked in column H and exit if not
If Target.Column = 8 Then  ' Rest code for processing row in column H
' Set auditRow based on the double-clicked cell in column H
Set auditRow = Sheets("Key Audit").Rows(Target.row).Range("A" & Target.row & ":H" & Target.row)
Dim returnSheet As Worksheet, allocSheet As Worksheet, auditSheet As Worksheet
Set returnSheet = Sheets("Key Return")
Set allocSheet = Sheets("Key Allocation")
Set auditSheet = Sheets("Key Audit")
' Confirmation message
If MsgBox("Would you like to record details of a Lost or Returned Key?", vbYesNo) = vbNo Then
Application.EnableEvents = True
ThisWorkbook.Worksheets("Key Audit").Protect password:="edit"
Exit Sub
End If

  ' Password storage cell
  Dim passwordCell As Range
  Set passwordCell = Range("J1")
  ' Password Retrieval
  Dim storedPassword As String
  storedPassword = passwordCell.Value
  ' Password Prompt
  Dim enteredPassword As String
  enteredPassword = InputBox("Enter Password to access Key Return sheet:", "Password Required")
  ' Check entered password
  If enteredPassword <> storedPassword Then
    MsgBox "Incorrect Password. Access Denied.", vbExclamation
    Application.EnableEvents = True
    ThisWorkbook.Worksheets("Key Audit").Protect password:="edit"
    Exit Sub
  End If

' Find matching row in "Key Allocation" sheet
Set allocRow = allocSheet.Rows.Find(auditRow.Range("A1:H1"), LookIn:=xlValues, LookAt:=xlWhole)

' Check if matching row found
If allocRow Is Nothing Then
MsgBox "Matching row not found in 'Key Allocation' sheet.", vbExclamation
Application.EnableEvents = True
ThisWorkbook.Worksheets("Key Audit").Protect password:="edit"
Exit Sub
End If

allocSheet.Visible = True
' Copy specific range (A:H) from "Key Allocation" to "Key Return"
allocRow.EntireRow.Copy returnSheet.Rows(returnSheet.Rows.Count).End(xlUp).Offset(1).EntireRow
' Delete matching row from "Key Allocation" sheet
allocRow.EntireRow.Delete Shift:=xlUp
allocSheet.Visible = False
Application.Wait Now + TimeValue("00:00:01")
' Unhide and activate "Key Return" sheet (using Visible property - recommended for all versions)
returnSheet.Visible = True ' OR use Option 2 or 3 based on your Excel version (see previous comments)
returnSheet.Activate
        End If
        
    Dim auditSheetRow As Long
    Dim resetValue As Variant
    Dim cell As Range
    Dim i As Integer
    
    ' Clear range A4:H30 in auditSheet before copying
    auditSheet.Range("A4:H300").ClearContents

    resetValue = auditSheet.Range("A2").Value
    auditSheetRow = auditSheet.Cells(auditSheet.Rows.Count, 1).End(xlUp).row + 1

    For Each cell In allocSheet.Range("A2:A" & allocSheet.Cells(allocSheet.Rows.Count, 1).End(xlUp).row)
        If cell.Value = resetValue And allocSheet.Cells(cell.row, 8).Value <> Date Then
            With auditSheet.Rows(auditSheetRow)
                For i = 1 To 8 ' Copying only columns A to H
                    If i <> 9 Then ' Excluding column I
                        .Cells(1, i).Value = cell.EntireRow.Cells(1, i).Value
                    End If
                Next i
            End With
            auditSheetRow = auditSheetRow + 1
        End If
    Next cell

    Application.EnableEvents = True
    ThisWorkbook.Worksheets("Key Audit").Unprotect password:="edit"
    ThisWorkbook.Worksheets("Key Return").Unprotect password:="edit"
    ThisWorkbook.Worksheets("Key Allocation").Unprotect password:="edit"

    Cancel = True  ' Prevent edit mode
End Sub