extracting info to another worksheet

[Rezez88]

Hey guys,

I've got a problem with extracting information. I have attached an example of the worksheet I am currently working on.
What I want is whenever the Status (Column H) is selected to "Closed", I want certain columns of that row (say E,G and H) to get extracted in Sheet3.

I'm a newbie to Excel, so any help on this would be appreciated.

Cheers,

Pete.

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

This method allows you to change multiple cells at once and it will process them all correctly, instead of just exiting the sub.

[Rezez88]

Thanks JBeaucaire,

Would it be possible to explain the lines:

Please Login or Register  to view this content.

Sorry to bother you, but I want to understand whats actually asking.

Cheers,
Pete

[Rezez88]

Also, why is this line required?

Please Login or Register  to view this content.

Cheers

[JBeaucaire]

Looking at your sheet again, I noticed a problem formula in column I. The formula uses the TODAY() function to display a date. You do realize that each time you open the sheet all those formulas will update to display today's date, not the first day it was resolved?

As such, since you're using VBA anyway, I'd remove the formula and let VBA enter the date, too. It's been added here along with the comments you've requested:

Please Login or Register  to view this content.

[Rezez88]

JBeaucaire, you are honestly a legend mate!! Thanks for also pointing out the silly error I made with the time and providing me with a solution.

I'm sorry if i'm asking for too much but is it possible to have closed items 'shifted' onto another sheet??
What I mean is, at the moment I have a condensed summary on worksheet 3 and I also want the whole row I have closed to be able to be removed from the main sheet and onto another sheet at a later date, say ''removed faults' sheet. This sheet will keep adding the new 'closed' faults to the top of the row and in the mean time, once removed from the main sheet also get deleted from the summary sheet.

Sorry if i'm not wording it properly, but any (more) help would be appreciated....

Cheers,
Pete

[JBeaucaire]

What/When/How do you see this movement being triggered?

I would use a Worksheet_Activate on this "other sheet" that does an AutoFilter on the main sheet for the Faults and then copies them as a whole to the top of the "other sheet", deleting them from the main sheet, then turning off the AutoFilter. So any time you want to "move" the fault, you would need only to activate this "other sheet".

Create a modified workbook and see if you can get an autofilter to copy the rows, we can tweak that together to make it cut/delete from the main sheet.

[Rezez88]

No worries, thanks mate i'll get working on that.. (it might take a little while though)

Just another question in the mean time. Is there any way to get my priorities coloured? All in white font, with "HIGH" filled in red, "MEDIUM" filled in blue, and "LOW" filled in light green. The problem is I already have three conditions (formatting) running and I don't know how I would code the above? Case statement possibly?? How would I choose the colours in code??

Cheers,
Pete

[Rezez88]

Guys,

I have attached where I'm at atm. I've added a delete row macro which is assigned to the delete row button.
Can you have a look and let me know whether this is the best way to do it or not. When the row is 'Closed', it is saved in the Summary Sheet (info required by externals). When the faults are resolved, I can delete the row and it should be moved to the Removed faults sheet and consequently removed from the Summary sheet.
There is still a problem with the file, it looks like there might be a conflict with the code you provided? Please let me know. Any help from anyone will be appreciated.

Cheers,
Pete

[JBeaucaire]

The worksheet change event I gave you didn't include making more changes to the active sheet. When you do that, you have to turn off macros first, else the ws_change macro can keep triggering itself over and over...could be problematic.

My edits on your updated version of that macro:

Please Login or Register  to view this content.

Other macro edits:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JBeaucaire]

Here's more, this adds moving the row to Removed Faults when it's set as "closed".

Please Login or Register  to view this content.