Excel Formula Closed

Question

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.

Configuration: Windows 7 / Internet Explorer 9.0

TrowaD · Answer

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

Excel Formula

1 response

Similar discussions

Subscribe To Our Newsletter!