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

[william516]

Ok this is hard to explain in the title and honestly I'm not sure what to write for it. Here is the setup and what I need it to do if possible.
When an inspector clicks on the results column and picks "FAILED" or "DAMAGED" when on the "INITIATING DEVICES" page that device and information will be copied to the "FAILED DEVICES" page at the next available row, this is done automatically with no user input. Now if the inspector were to go to the "FAILED DEVICES" page and select that failed device from and change the results to "PASS" or "REPLACED" or "REPAIRED" etc it would be removed from the "FAILED DEVICES" page and the status would be updated on the "INITIATING DEVICES" page. I have the first part working but can not figure out how to get it to work in reverse. Have been messing with compare commands but with no luck.

Here is the setup on both pages
Column A = Address
Column B = Device type
Column C = Location
Column D = Part #
Column E = Results (drop down list to choose from)
Column F = Notes (yes / no)
Column G = Msg. Chg. (yes / no)
Column H = Sens

Only columns A to E need to be brought to the other page,

here is the code on the "INITIATING DEVICES" page

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 7 And UCase(Target.Value) = "YES" Then
        Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value
        Application.Goto Sheets("MESSAGE CHANGES").Cells(Rows.Count, 1).End(xlUp).Offset(, 3)

    End If
    
    
     If Target.Column = 6 And UCase(Target.Value) = "YES" Then
        Sheets("DEVICE NOTES").Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value
        Application.Goto Sheets("DEVICE NOTES").Cells(Rows.Count, 1).End(xlUp).Offset(, 3)

    End If
    
    
     If Target.Column = 5 And UCase(Target.Value) = "FAIL" Or Target.Column = 5 And UCase(Target.Value) = "DAMAGED" Then
        Sheets("FAILED DEVICES").Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = Sheets("INITIATING DEVICES").Cells(Target.Row, 1).Resize(, 3).Value
     
    
    End If

Thanks for any help, already have learned so much from reading and messing around

[Excel Fox]

You might be needing the code for each of those three sheets, right? And since the layout is the same, you can use the same code for all of them. But the question is, if the status is changed back to "PASS" or "REPLACED" or "REPAIRED", since you want to update that status on to the INITIATING DEVICES page, and remove the entire row from the other sheet, what would happen to the rows of data below that line? Should it be moved up?

[Excel Fox]

By the way, if columns A to E need to be brought back, what are you only copying 3 columns to the INITIATING DEVICES sheet?

[william516]

Ok I guess in short the answer would be that any lines removed from the "FAILED DEVICES" sheet would then cause all the lower ones to move up, in other words no open spaces.

As for columns A-E. When the item is marked as "FAIL" or some other failed keyword, columns A-E of that device would be copied to the "FAILED DEVICES" sheet however when the inspector decides or corrects a problem and changes column "E" on the "FAILED DEVICES" sheet the status of only "E" would be updated on the "INITIATING DEVICES" page. Simply because that information never moved so all we care about is updating column "E" if possible.

[william516]

Here is a link to the file, the file upload on this site is too small of a limit to post it on here. So here is the file link if anyone can help. Thanks again for any help.
Zippyshare.com - SAMPLE_insepction_converted_061913.xlsm

[bakerman]

I don't have an account for that site, but i've one question though.
Is there a Unique Identifier in every row in INITIATING DEVICES or a combination (f.i device type and location or device type and part) that makes every row unique to any other row ?
The reason for this is when a row was transferred to FAILED DEVICES and afterwords the status is changed we could use that UI or combination to trace back the row in INITIATING DEVICES to change the status there.

[william516]

To answer your question, as of right now there is not a specific number applied to each line. 99% of the time though the combination of columns A, B, C should always produce a unique identifier. I'm assuming then that I would need to add another column prior to A and change all the code so that anything that was Column A is now B and so forth. The other problem with adding anymore information is the page size. Another column will push the page limit and it would have to be printed in landscape mode. Would it be possible to create another column but leave it hidden so that is not printed or displayed but continue to be used for information.

The other problem would be that unique number would be required to be added to each line unless it would be possible to add each number as the information is entered or pasted into the worksheet.

[bakerman]

You could also add a column at the end and leave it out of the printarea, so that wouldn't pose a problem.
The only thing is that you will have to come up with something that, in order to make your system work, makes every row unique to another.
In that last column you could f.i. use a formula to make a concatenation of some parts of the first three columns so to create a UI.
Put your current data into a Table and the formula is copied automatically with every row of data you add.

[william516]

Wow you lost me with that, I feel really stupid. Lol. What I was thinking was to add a column "k" that would not be printed and would simply be used for calculations. So that column would contain a number starting at #1 and then increasing by one for each line of text is entered or copied and pasted into the program. Actually if the numbers are not going to be seen or printed the. I could simply make each row a number. That would solve the problem.

The question then becomes how do I get the two pages to update each other. So for example lets say that row # 1 device has been tagged as failed, now that row is automatically copied to the failed devices page from another macro. Now that it is on that page a user uses the dropdown list to chose replaced. At that moment the device would be removed from the failed devices page and results column on the initiating devices page would be updated to show as replaced. I guess I would simply need to compare the rows from each page then to get this result?

[bakerman]

I've put it in a small sample file so you can see it at work.
I guess this is what you're aiming for.