Clear Content of a Row whilst keeping formula when a condition is met.

[Nezzer21]

Sorry I am new to VBA's so apology in advance!

I am working on a workbook with 4 sheets on it

Sheet 1 - is grand - collection of data calcuatations that I have a number of calcuatations on this page which I understand and can use.

Sheet 2 (called level2) and Sheet 3 ( called Safeguarding) has 12 column's in use to capture data such as A1 Name A2 DOB, A3 ID number, ....... up to Column L which has a drop down box for 'Active' or 'Closed' .
There are a number of formulas and drop down boxes in the cells ( such as Column G is named 'Language' and has a drop down list of languages in each cell). The Range is from A2:L50 on each sheet.

Sheet 4 is called 'CLOSED'

I require a VBA to move the entire row from either sheet 2 or sheet 3 onto the next available row in sheet 4 ( CLOSED) when 'Closed' is entered into any of the cells in Column L. without loosing the formatting in both sheet 2 and 3. I can use a control button on each sheet if it is simpler.

The sheet will be updated monthly

If I could add a 'date closed' date automatically into sheet 4 Column M that would be even better!

I have tried multiple codes but I am a complete beginner!

thank you.

 

[onlyadrafter]

Hello,

This code needs to go into both the source sheet code windows

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
    If Target = "Closed" Then
        Target.EntireRow.Copy
        With Sheets("Closed")
            .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
            .Range("M" & .Range("A" & .Rows.Count).End(xlUp).Row).Value = Date
            Target.EntireRow.ClearContents
        End With
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub

Does it work as expected?