Automatically exporting a cell's data based on another cell's value

[pgabhart]

I am working on a spreadsheet to keep a live status on all the trucks in our company's fleet. This is to avoid our truck mechanics having to call our shop supervisor and then him having to call our dispatcher to update her on a trucks status. Using this spreadsheet the mechanic can update this sheet and the supervisor and dispatcher will receive updates whenever a truck status is changed.

What I'm struggling with; I want to be able to copy any comments about why a truck was down and have them export to a second sheet labeled 'Comment History'. On this sheet I would like the truck number, date and comment to export over. I was able to record a basic macro to copy all of that over but I wasn't able to get it to paste over in the next available row. I think I need to write code in VBA to accomplish this.

I attached a picture of the spreadsheet to give you an idea of what I'm looking at.

 

[mumps]

It is hard to work with a picture.
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[pgabhart]

MTC Truck Availabilty (Macro experimenting) - Copy.xlsm

Shared with Dropbox

www.dropbox.com

Hopefully that worked. First time using Dropbox.

Essentially I would like whenever the data in Cell B2 is switched from 'NO' to 'Yes' I want the info in column A, C and E to transfer to the 'Comment History' worksheet.
This way when a truck becomes available again we will have a log of when it down and why. Then we can filter those results at a later date if needed.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column B. The data will be automatically copied to Comment History.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 2 Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "NO" Then
        With Sheets("Comment History")
            Intersect(Rows(Target.Row), Range("A:A,C:C,E:E")).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[pgabhart]

That seems to transfer over the comments if they comment is in that column before I change the data in column B to NO. Our mechanics would most likely change the status then add comments. Any ideas on how to combat this?

 

[mumps]

Is the status entered only if "NO" is entered in column B?

 

[pgabhart]

There will be a YES/NO for all trucks at all times. However if a truck is available (green) there will not be any comments that need transferred. Only when NO is in Column B.
Would it be easier to make it to where it transfers over the comments whenever we switch the status BACK to YES? Because by then we would already have the comments of why the truck is down entered in Column E?

I truly appreciate the help!

 

[mumps]

If you want to transfer the data when you select "YES", just change to "NO" to "YES" in the code. If you want to transfer the data when a comment is entered, change the 2 in the third line of code to 5.