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

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