Moving entire row to another worksheet if a certain word is enter into a specific column

[CityInspector]

Sheet One titled "Active Tests" has columns as follows:

Vendor (A) Bid # (B) item description (C) Using Dept (D) Random/Issue (E) start date (F) finish date (G) pass/fail (H) completed (I)

When the pass/faill has pass put in it and yes under completion i want that whole row to go to a sheet called passed tests. When Fail is put in the pass/fail column and yes put in the completed column I want that whole row to go a sheet called failed tests. In both cases I also want it to delete the whole row from the first sheet. Is this possible? I am not very good at Excel 2007. Thanks for any help you can provide.

*****edited to show column lettering

[jeffreybrown]

Hi CityInspector,

See if this works for you. Add info to the Main Data tab in all columns, then in the Pass/Fail column select on of the two choices from the drop down and then select Yes from the last column and the transfer/delete will all happen through the macro.

[CityInspector]

Thanks. I will have to check it on monday when I return to work. I really appreciate it.

[jeffreybrown]

You are very welcome and let me know if I can help any further

[CityInspector]

It works. Thanks for your help. I'm sure I will be back with more questions. Really glad I found this place

[jeffreybrown]

I just downloaded the attachment on this thread and it has three tabs (Main Data, Failed Tests, Passed Tests).

On the Main Data tab I enter Pass in column H with Yes in column I. As soon as I do this the row is cut and transferred to the Passed Tests Tab.

Can you download it again?

[CityInspector]

I edited my post. For some reason the tabs were not showing up to change sheets but I got them to show up. Again, thanks for your help.

[jeffreybrown]

You are very welcome and glad it worked out for you. Thanks for the feedback

--------------------------------------------------------------------------------

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[CityInspector]

With the file that you uploaded, is there a way to change the label for the last column. It is "Completed" now but I need it to be labeled as Done. When I try to change it, it deletes the entire row. Thanks.

[jeffreybrown]

Sorry my fault, did not even cross my mind to do this.

At the top of the macro where the other "Exit sub" statements are found, let's add one more...

Please Login or Register  to view this content.

[CityInspector]

That did the trick. Thanks for the help.

[Tejas.T]

Hi CityInspector,

The code suggested by jeffreybrown is just perfect.

Still I would like to suggest one more enhancement. In that if you directly add "Pass" and "Yes" and keep all other cells blank in data sheet, the row doesn't move correctly to Passed/Failed sheet.

To overcome that, used the following code.

Please Login or Register  to view this content.

Thanks,
Tejas

[CityInspector]

Tejas,

Not sure I understand what you mean. I would never input pass and yes if the rest of the row is blank. I did try to put pass and yes in the first sheet and it does move that info to the pass sheet. Can you please explain what you mean a little more. Thanks.

[Tejas.T]

Do the following TWO times consecutively:

Put "Pass" and "Yes" in first sheet.

Now go to passed sheet and you will find only one row in Passed sheet (not two).

[jeffreybrown]

@tejastanna77, May be a good idea to explain your change to the macro just in case it isn't apparently obvious.

@CityInspector, I understand the change Tejas made, but like you said...

I would never input pass and yes if the rest of the row is blank

...even with this said, Tejas you still have not done anything about the row being void of data. You have just determined a different column to set the last row.

[CityInspector]

the whole point of putting pass/fail and yes is because the row will already have the info in the cells so i would never meet the conditions while the cells are blank so i don't see the need for a change. Or am I missing something?

[jeffreybrown]

No, to me you are not missing anything. If all data is full then good to go.

[CityInspector]

No, to me you are not missing anything. If all data is full then good to go.

Thanks, I'm going to leave it as is.

[jeffreybrown]

Sure enough, let me now if you run into any trouble.

[GenericPat]

For me this code cuts and copies like you've all mentionned but when I do a second row, the row on the other sheet is replaced by the new row... How do I add them to the next available row instead of replacing?

[jeffreybrown]

@GenericPat,

The code is written with the intention of adding a new row of data versus copying over the current row of data; however, if we look back at the point Tejas brought up, he/she suggested to use column I to establish the last row versus column A. A great observation.

Reason:
It all lies in whether data is entered in column A:G of the source sheet or not.

Column I holds the trigger for transferring data. Select "Yes" and that row of data is copied to the sheet that matches the name in the code.

When the row is transferred, the code has to establish the last row to know where to copy too.

Let's say the first time you transfer a row you do not fill the data elements in columns A:G. The last row on the sheet being pasted too is row 2; however, if you past one more row to the same sheet, the last row will still be row 2 because column A does not contain any data.

Tejas suggested using column I as the last row indicator as to prevent a user from not filling out columns A:G. This column could be the best indicator for setting the last row, but cityinspector was not concerned because column A:G would never be void of any data prior to selecting "Yes" in column I.

Column A from post #2 equals column 1
Column I from post #12 equals column 9

Please Login or Register  to view this content.

All of this makes sense in my head and hopefully it translates to you also...

[Tejas.T]

@jeffreybrown: Good explanation. Thanks.
@GenericPat: And if you put validation like User must fill some data in column A, then code suggested by jeffreybrown would be just "Perfect"