Macro to copy specific cells/rows to new sheets

Hi there,
Sorry to have to rely on this forum again.
I have done some reserach and found macros from others, but nothing is entirely specific to my query.

I have attached the sheet to make it easier to understand.

The problems I have on this, are because column E is a stage, and each time the cell is change to reflect the status, i would lose the information required. so as follows...

1. When a cell is selected in column E as 'Trial Agreed', a date will be entered in column F. I would like a macro to run whenever a date/value is entered in a cell in column F. The macro needs to copy and paste this row (if possible just certain columns) into the sheet titled 'Trial Agreed'

2. If the status 'Lost' is selected in column E, then a macro runs to CUT and PASTE (or something similar) the whole row into the sheet 'Lost'.

3. If the status 'New Customer' is selected in column E, then a macro runs to CUT and PASTE (or something similar) the whole row into the sheet 'New Customer'



Appreciate your help.
Thanks Simon

Awesome awesome.


just wondering perhaps cut and paste was the wrong option, as the row deletes, and eventually I would be left with no rows.

is it possible to delete the values from the original sheet (lead tracking), and then copy and paste the bottom row (the row above the total - row 113 i think, which will never have data in it)
By doing it this way, I will still have the formulas in the row where the data got removed when it moved to another sheet.
if this is a clumsy way of doin it, then open to suggestion.


thanks thanks

Great. Perfect idea.
'Lost' works perfectly
Although.
'New Customer' crashes excel every time i try to select it (first it comes up with the macro debug error message) but crashes before i get chance to do anything.

Also if thats a simple cure... then one last question.

is it possible to create a warnnig message on selection of the cell.. something like 'changing status - are you sure?
many thanks

Sorry, it seems that it crashes when the status is selected, when all the other cells are blank.
so don't think it likes trying to move a row with no values.

don't worry though. I won't need to do that, so not a problem.


but as previously mentioned would be great to have a warning box appear. seeing as it quite a severe function that it does by moving the row, it would be great for the user to have a warning.
not sure if that's possible?

thank you, appreciate always

Absolutely perfect. Thanks Jerry
Sorry I know i said it was my last question. but one other thing. something which i have been thinking i cud use for many other spreadsheets if I knew how....

On the trial agreed sheet, I actually needed to use one more column on the trial agreed sheet. i needed to know the date from column f.
although once you select trial agreed - its too late to enter the date, because by that time the row has already copied.

is it possible to incorporate a macro where if a certain entry is made, e.g. if any cell in column E is entered as "Trial Agreed', then a box opens to ask for the the cell in column F (same row) to be filled in, almost like a form.
or is this above excel?
and then if this worked, the macro you wrote could run after as required

cheers simon

Great stuff. Exactly what I wanted. Think I will have to learn a little more though to be able to use that myself in other applications.

I just have one problem though. The macro deleting a row, is causing a problem on another sheet that pulls from it.

The sheet 'Phase Calculations' loses formula from a row, everytime the status 'Lost' or 'New Customer' macro runs on the 'Pipeline Details' sheet.

I have attached the sheet so it will make sense.

The only thing I could suggest is to run a macro after that copys the formula from the last row on the 'Phase Calculations' sheet to reinstate the formula. I suppose this is clumsy though.

Sheets("Phase Calculations").Select
Rows("114:114").Select
Selection.Copy
Rows("113:113").Select
Range(Selection, Selection.End(xlUp)).Select
Rows("13:113").Select
Range("A113").Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Is there a better way?

Genius.
THANKS THANKS THANKS