Change code from Worksheet Change event to Worksheet Deactivate event

[mtaylor50]

The Worksheet Change code below moves records that meet one of three criteria in column C of the sheet named “All Orgs” to another sheet named “GrntClsd”; the code then clears the moved records from the “All Orgs” sheet. I’m a relative newby to VBA, so this code may not be the most elegant, but it works.
However, I want to change the code so it fires only when the user leaves the “All Orgs” worksheet, i.e., I want to convert the code from a Worksheet Change event to a Worksheet Deactivate event. I have tried many variations and searched the forum for hours for a solution, but without success. Any help will be much appreciated. Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub
  If Target.Cells.Count > 1 Then Exit Sub
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Dim lRow As Long
  lRow = Range("A" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C2:C" & lRow)
      If cell.Value = "Grant Closed" Or cell.Value = "App Declined" Or cell.Value = "LOI Declined" Then
      ActiveSheet.Unprotect
      Range(Cells(cell.Row, "A"), Cells(cell.Row, "V")).Copy
      Sheets("ClsdGrnts").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      Range(Cells(cell.Row, "A"), Cells(cell.Row, "R")).ClearContents
      End If
    Next    
  Application.ScreenUpdating = True
  Range("A2").Select
  Application.EnableEvents = True
  Application.CutCopyMode = False
End Sub

 

[dmt32]

hi,
untested but see if this does what you want

Place in your sheets code page

Private Sub Worksheet_Deactivate()
    Dim lRow As Long
    Dim Cell As Range


    With Application
        .EnableEvents = False: .ScreenUpdating = False
    End With
    Me.Unprotect
    lRow = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
    For Each Cell In Me.Range("A2:A" & lRow)
    Select Case Cell.Offset(, 2).Value
      Case "Grant Closed", "App Declined", "LOI Declined"
      Cell.Resize(, 22).Copy
        With Sheets("ClsdGrnts")
            .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
      Cell.Resize(, 18).ClearContents
      End Select
      Application.CutCopyMode = False
    Next Cell
    With Application
        .EnableEvents = True: .ScreenUpdating = True
    End With
End Sub

Dave


ALWAYS MAKE A BACKUP WHEN TESTING NEW CODE

 

[mtaylor50]

Dave,
The code you provided seems to work perfectly - thank you VERY much. I really appreciate your help and the terrific resource this forum provides.
Mark

 

[dmt32]

Dave,
The code you provided seems to work perfectly - thank you VERY much. I really appreciate your help and the terrific resource this forum provides.
Mark

Hi Mark,
most welcome & thanks for feedback.

Dave