Excel Formula

Closed
Help - Feb 21, 2012 at 08:13 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Feb 23, 2012 at 09:18 AM
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

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Feb 23, 2012 at 09:18 AM
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
0