VBA - Macro to move rows from one worksheet to another

[jonboy0766]

Hi, new to VBA...my wife bought me VBA for Dummies for Christmas So I'm trying..
At work I keep an Action Register used by many departments so it's lengthy. What I'm trying to do is create a macro that moves actions of a specific status i.e. pending, in progress, complete etc to a different worksheet. So if I run the macro it would move all the completed rows/actions into a worksheet call completed actions etc.
I've tried recording a macro using 'if' but it simply copies one row over and over writes what was previously there - so partial success of sorts but of no use.
Any help appreciated.

[Mumps1]

A Worksheet_Change macro could do this for you automatically as you enter the 'status'. In which column is the 'status'? Do you enter it manually or do you choose the status from a drop down list? Do you want to move the rows to the "Completed Actions" worksheet only if the status is "Complete" or if the status is any of "pending, in progress, complete"?

[jonboy0766]

Thanks MUMPS1, yes that is exactly what I want to do and exactly as I envisage it working. Is worksheet_change a function?

[Mumps1]

OK. I need answers to the questions I put in Post #2. If you can attach a copy of your file, that would make things much easier.

[jonboy0766]

Hi Mumps, okay. The status is in Column E and is chosen from a drop down list. Also only to move when the status indicates Complete.

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for the sheet containing your data and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. The macro is triggered by a change in any cell in column E so you must make sure that the data in all the other columns is entered first and the selection in column E is made last. Also the macro assumes that column A will always have data in it.

Please Login or Register  to view this content.