Macro to cut row and paste into secondary worksheet based on dropdown value

Hello

This is my first post here and I am impressed with the level of expertise on display with regard to VBA - at which I am a novice.
I've searched through the posts and looked at several similar code examples and tried a few, but nothing is working as I hoped and I am at a loss with the language!!
Could you please take a look at my code below and what I need to achieve, and lend a hand?

So, I am using Excel 2010 and have a customer prospects worksheet called "Ongoing", one called "WON" and a third called "LOSS"
All sheets contain the same headers (8 rows high) with approx 20 columns of data. The customer list starts at row 9.

In Ongoing I have a status dropdown in column B of validated data items (e.g. won, callback, completed etc) and several other columns containing calculations formulae to calculate cost, avg etc.

I also have another macro that allows the user to click a button - and on doing so, it inserts a new line (row 9) at the top of Ongoing for a new entry and copies the formulas from below, and moves the existing entries down.

What I would like to do now is when the user selects a status for any customer (there could be a hundred or more listed) of "CLOSED - LOSS" a pop up or alert should ask permission (E.g. Press OK to remove and archive this entry) to cut that line and copy it across to the LOSS worksheet (it should place it after the last row on that sheet) and remove the line from Ongoing, but without affecting the other rows, macro or formulas. Similarly, if the user selects "COMPLETE - WON" it should also provide the alert asking for permission to remove to the WON sheet (and also place it after the last row on that sheet).

Here is the code I have so far, which I found on another post in this forum - but it's not working and I am not sure how to modify it to my needs.

Thanks in advance.

Please Login or Register  to view this content.

Let's do this in realtime as you enter those values. Right-click the sheet tab and select VIEW CODE to open the sheet module, then paste in this event macro:

Please Login or Register  to view this content.

NOTE: There can only be 1 Worksheet_Change event macro in any specific sheet module, so if you already have one, you'll need to merge their functions into one larger macro.

TIP of the day: Read macros given to you line by line looking for mistakes

Even though you may not fully understand it, read it all anyway, several times. The more you do, the more familiar it will become and it will start to make sense even though noone has specifically explained each line. Trust me, it will.

Then you might have spotted the fact that your sheet names didn't match the code, the values being looked for didn't match...

Please Login or Register  to view this content.

One further question...

If I wanted to reverse this move (e.g. back from closed to ongoing) - how could I do it so that it re-inserts the row back into ongoing, but say, above the totals row?
I had a little play with the code (below) and it works, but it inserts the code after the total row.
I guess it would have something to do with this line:
StatCell.EntireRow.Copy Sheets("Ongoing").Range("A" & Rows.Count).End(xlUp).Offset(1)
So how to modify that to insert before the Totals line, (but after any existing row data - and also preserve the formulas?)

Please Login or Register  to view this content.

For this to work correctly, we need to insure there is always a blank row between the totals and the data above. Then this should do it:

Please Login or Register  to view this content.

You need to eyeball your column A on those sheets, then. The macro is jumping UP from the bottom of the sheet TWICE. It should first run into the TOTAL cell on the first jump, then jump up to the bottom of the data from there. If that's not happening, then something else is in column A. You might need to jump more than twice.

Please Login or Register  to view this content.

When you jump the bottom of column A, all the way to the bottom., then do a CTRL-UPARROW twice, where does it put you?

Take a shot at coding that.

Actually, I think the simpler solution is to change your formulas for totals at the bottom of C and D to include that blank row directly above. That should resolve it.