Copy Row To Another Sheet On Change Of Value/Update Value To Another Sheet On Change

[william516]

Thanks again for all your hard work, I'm still using the old codes but I will be sure to add this to the sheet. However I have managed to break a good thing and I'm sorry. All I did was add a column prior to the part # column. Now I knew that this would require me to change all VBA coding and increment the number to "6" from 5 because now it is in column "F" and no longer "E". I changed that on both the failed page and the initiating devices page however I no longer can get it to copy things correctly. When choosing "FAIL" it now seems to copy information from random areas, I can't even figure where its getting it from. I thought that I did everything right. I will try and put your NEW code with the array in it as I like the way it works much better and its easy to add and subtract words from it.

What did I do wrong.

Here is the list of changes from prev. version

portrait changed to landscape both pages
dependent dropdown list for column "E" "part # and column "D" Manufactuer
Countif added to several other columns to count keywords and display a number on executive summary page.

Sorry to break such a good thing, I got the other codes to work so I must be missing something.

Thanks
excel grrrrrrrrr

[william516]

Ok I gave the coding listed below a shot on the failed page and changed the column number from 5 to 6 because of the added column that was added since the code. The results were not all that great, I must have really done something wrong.

When using the new code anytime something is entered manually into the column A,B,C,D etc the second it is done it is erased and so is the whole row. This even took away my merged cells that used to be in rows 3-5 that were my headings for the page. The only way I could keep it from continuing was to get rid of that new code. I'm assuming I have done something wrong here. I could send you the few pages if you want to look at it. I have the new code simply commented off with ' in each line to keep from having problems

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CheckArr() As Variant
    If Target.Count > 1 Then Exit Sub
    CheckArr = Array("PASS", "REPLACED", "REPAIRED")
    On Error Resume Next
    If Target.Column = 6 And Not IsEmpty(Application.WorksheetFunction.Match(UCase(Target.Value), CheckArr(), 0)) Then
        Application.EnableEvents = False
        Sheets("INITIATING DEVICES").Columns(11).Find(Cells(Target.Row, 6).Value, , xlValues, xlWhole).Offset(, -6) = Target.Value
        Target.EntireRow.Delete xlUp
        Application.EnableEvents = True
    End If

End Sub

Thanks, sorry for breaking things

[william516]

Ok I took a quick look at a few things and after reading figured out what some of the values actually mean that I was missing. The column 11 has now changed to column 12. Upon changing that value things started to work a little better but still nothing perfect. I'm thinking that is because the format of columns for "Failed devices" was not the same as the "Initiating Devices" page. The columns that need to be displayed for the failed devices page are as followed

a (Address)
b (device Type)
c (location)
d (Manu)
e (Part #)
f (results)
J (Date)
M (does not need to be displayed but will now be the unique identifier #)

This format will also be the just about the same for the missed devices page as well.

See I told you I broke it. How can I change or modify the "NEW" array code to display just the columns I need above.

Thanks

[bakerman]

The smarttest thing to do is to post a new file with the new setup.
Put columnheaders on each sheet so I can see what has to be transferred to where.
Also put in all the code you are using so far.

[william516]

I am unable to load the file, can you email me or something and I can send you the file. It is too big to fit on this site. I am trying to send a link to the file as well. I have erased all information and format but it is still too big.





Zippyshare.com - Help_inspection_071013.xlsm

Thanks again

[bakerman]

Based on the file you've posted i guess the main problem is that column A isn't always fillde with data. Is this also the case in the actual file you're working in ?
If so the only thing to do is to wrewrite all code in function of a column that is always filled with data and also always is copied thus present in every sheet you use the data of initiating devices in.
I seem to remember you once said that column C is always filled with data ??

[william516]

ok I tried to modify the code to be using column "C" I assume this would be number three in the code. Here is what I came up with.

   '(replace if new code fails)If Target.Column = 5 And UCase(Target.Value) = "FAIL" Or Target.Column = 5 And UCase(Target.Value) = "DAMAGED" Then
        '(replace if new codes fails)Sheets("FAILED DEVICES").Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value
     If Target.Column = 6 And UCase(Target.Value) = "FAIL" Or UCase(Target.Value) = "DAMAGED" Then
        Application.EnableEvents = False
        Sheets("FAILED DEVICES").Cells(Rows.Count, 3).End(xlUp)(2).Resize(, 5) = Sheets("INITIATING DEVICES").Cells(Target.Row, 3).Resize(, 5).Value
        Sheets("FAILED DEVICES").Cells(Rows.Count, 3).End(xlUp).Offset(, 5) = Sheets("INITIATING DEVICES").Cells(Target.Row, 11).Value
        Application.EnableEvents = True

Could you explain what the codes mean as well? How would I change this to be an array like the other code you posted?
Nothing happens at all when I use the code above. I do not get an error or anything no information is copied. Column "C" is something that should always be filled in so it should be a safe choice to use as a comparison. I also assume that since I added a column I need to change a "5" to a "6" as well. But this still does nothing.

[william516]

Still have no luck with getting it to work right, I went back to the old version for now, until I can get some help or better understanding of how or what that code means.

[bakerman]

OK, here we go.

[william516]

Thanks again for all your hard work, I have had a chance to play around with some things and it is fitting together pretty good. A few "test subjects" have tried this out as well and have made a couple discoveries. I have fixed most but can't figure out how to fix this issue.

If the devices that are missed are searched for and copied to the missed devices page and then the user clicks on pass or fail or any other option included in the array or dropdown box. It is updated just like it should however the cells do not actually signal a change. So for example a devices is missed and the inspector tested it and it failed the test. He is on the missed devices page and clicks fail from that page. The item will not show up on the failed devices page. It will however show the correct status and color format on the initiating devices page. Also the date of test is not filled in. I believe that all these commands require a change in cell state.

If I manually type in fail or choose fail from the drop down list it will do just what it is supposed to. So the problem must be with copying data is not signaling a change in the cells state so all macros that are looking for that change are not seeing it and failing to work. Any ideas how a pasted value in the cell can cause a change in cell state to work correctly?

Thanks the other things were all my fault and mostly data validation errors. Still learning.

Thanks again