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

Nezzer21

New Member
Joined
Sep 14, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Attachments

  • excel.PNG
    excel.PNG
    6 KB · Views: 6

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

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

VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,992
Members
415,873
Latest member
fuulhouse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top