Cut and paste entire row into new worksheet based on cell value

[langle_j]

Let me start by saying my VBA skills are at best very limited!

I have a large worksheet entitled Master List with over 1000 rows of data and "U" columns, with new rows being added on a daily basis by a number of individual users.

In Column R there is there is a status option of "OPEN" or "CLOSED" (all cells are filled)
I would like to assign a macro to a button which cuts and pastes all the "CLOSED" rows onto a new worksheet entitled CLOSED maintaining the column headers if possible. Also, to avoid gaps in the first worksheet delete the now empty rows.

The purpose of the macro is for myself to easily seperate the OPEN and CLOSED data, as only the OPEN entries need attention.

I have been looking around for code for this problem but am struggling a bit, "an idiots guide to...." would be most appreciated.

I am very new to forums, so if i have missed any necessary information please let me know.

Thanks,
Joe

[jraj1106]

Hi
Upload the sample file..

[jraj1106]

Hi
Check out this. But there is one problem. When the closed entry is cut and pasted into the "Closed" sheet, what abt the blank rows that will be formed in "Master List" sheet? Pls clear this... Also check the cut and paste operation..

cutpastedata.xlsm

Regards

[langle_j]

thanks that sample code works, but as you mentioned there are now blanks in the master list where the "closed" actions were cut.

is there a way for these to be removed by the same macro after the closed actions are cut?

also, when there is no "CLOSED" lines of data on the Master List when i run the macro then i get an error message run-time error '9': subscript out of range, could this be prevented and possibly a message box to say "no CLOSED actions found" could pop up instead?

[langle_j]

i have figured out how to remove the empty cells so do not worry about that, just the problem of the error message now!

[jraj1106]

Try this modified code:

cutpastedata.xlsm