Excel Formula

[Closed]
Report
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
Hello,

I am working on log spreadsheet. The concept is to keep a log of all of our near miss activities at work like a broken chair that needs to be fixed. Then when it is completed to automatically copy the near miss into a new tab.
For example, I have a row that has the Name of the employee, date, the near miss, and then if it was completed. If I write Yes then I want it to copy to my completed tab.

I am just not sure what kind of formula I can use.
If you need more information please let me know.

1 reply

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Help,

Macro's are irreversible, make backup of file before using macro. Better save then sorry.

I created to sheets: Data and Completed.

Both sheets have header in row 1.

Data sheet has 4 columns of data, Completed sheet has 3 columns of data (minus the completed column.).

Right-click on the sheets tab and select view code. Paste the following code in the window that opens:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Integer
If Intersect(Target, Range("D2:D" & Rows.Count)) Is Nothing Then Exit Sub
    If Target.Value = "Yes" Then
Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Copy
lRow = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Completed").Range("A" & lRow).PasteSpecial
    End If
Application.CutCopyMode = False
End Sub

Test code by entering Yes in column D of Data sheet.

Also consider the option to apply dropdownlist to enter Yes with two mouse-clicks.

Best regards,
Trowa