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 (http://www23.zippyshare.com/v/44056325/file.html)

[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.

[william516]

Awesome job, that works just like what I was looking for and I can modify the keywords if needed to add more. Now is there a way to make it so that column "K" will not be seen or printed but still be used as the identifier #. As it is right now it looks like choosing print will try and print the 2nd page and I don't really need that. Not only that but the numbers are formatted from 1-20021. So I'm trying to find an easy way that the 2nd page or the extended columns would be ignored from everything. I wont be the one that will be using this all that often so the less the inspector/user has to tinker with it the better. Thanks again for everything.

I tried hide column but then the Macros no longer work, so I'm guessing you can run a Macro with hidden cells?

[bakerman]

Put this in ThisWorkbook.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("INITIATING DEVICES")
.PageSetup.PrintArea = .Range("A1:H" & .Cells(Rows.Count, 1).End(xlUp).Row).Address
End With
End Sub

[william516]

Thanks alot for all the help, so far this works the best. I had a quick question. Could the above code be modified to find empty cell values. For example on the same sheet "initiating devices" for column E "results" would look for a value with no entry or blank value. Now it would need to look for columns A, B, C, to be filled in so that all the empty spots on the sheet are not calculated as well. Any empty value in "E" would have that row copied to the "missed devices" page.

Once on this sheet, an inspector could enter from a drop down list a new status such as "replaced" "pass" "fail" etc. once a value has been entered, the value would be copied to update column "E" on the initiating devices page and removed from the missed devices page. I have got it to work but the second a blank cell is filled in on the "E" column on the missed devices page it is removed. It is NOT updated on the other page and it doesn't matter what is entered any letter will cause the row to be removed.

[bakerman]

So if I understand correctly, you want to search column E for empty values. Once found there has to be a check wether columns A,B and C are filled with data.
If not complete then copy row to sheet MISSING DEVICES otherwise do nothing ?
When row is copied to sheet perform the same operation as for sheet FAILED DEVICES ?

[william516]

Yeah I messed myself up a little on that. It would need to be able to search for a few keywords such as "no access" and also blank or no value cells. Based on some inspections I'm looking at it looks like it should look for information in A or B or C it does not need to be all three. Several inspections do not have have addresses. Actually it could simply look at column "c" as this is the description of the device and is required for all inspections. So it could simply look for a value "text value" in column "C"

If value or text is found in column "C" and no value is found in column "E" "Results" then it should do the same thing as the failed devices. It should be copied to the "Missed Devices" page and then if a value is entered on that page. it should be updated.

If NO value is found in column "C" and no value is found in column "E" "Results" it should do nothing at all. This should keep the macro from pulling rows are not used or left empty for the rest of the worksheet. This can be run from a macro and I will simply link it to a virtual button.

That way when the user has finished the testing they can run a search for all missed devices, or no access devices, and try and find them later if needed.

Thanks

[bakerman]

Sorry for the delayed response but have another go at this one.

[william516]

No need to apologize, I fully understand. Thanks for everything. I will take a look and see how this all works. Have a great Holiday. Hope you get a day off.

[bakerman]

I certainly hope you have a nice one, but here in Belgium it's just another day at work. :(

[william516]

I'm on call here in the US as well. So I don't have a promise that I will be off.

Ok I looked at the code and the only thing that I see as a possible problem and its something I could probably work around is that if for some reason if the column "E" results on the "missed devices" page is selected as "fail" the code will remain and not be removed. Everything else works. I'm pretty sure I can just simply add that to the statement using an "OR" statement. Great work though and once again thanks.

Do you have any idea how to limit what cells are displayed on printed / converted to .PDF? I'm trying to get this information to print to .PDF file but the way I have it right now it wants to print every column including column "K" and I have #s from 1-20000. When I convert to PDF it attempts to keep all those numbers and then it becomes a 1000 plus pages long with just numbers. Is there a way to allow the formula to use a hidden column? or get VBA to use information that is in a hidden column?

[bakerman]

I've made some changes in the event-macros both on sheets Failed and Missing devices so it's easier to add or remove names that need to be checked instead of all those 'OR' arguments.
About exporting to PDF you could set a printarea in advance and then in ExportAsFixedFormat set the property IgnorePrintAreas to False. I haven't tested that yet but i presume it won't export outside that set area.

[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
:stick: 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 (http://www70.zippyshare.com/v/39013881/file.html)

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

[bakerman]

This should do the trick.