I'd keep them in place, but use Data|Filter|autofilter to show/hide what I want.

I think it makes life much more simple--especially when you're assigning blame
<bg.

But if you want, you could always sort by that field and cut the rows you want
and paste to the different sheet.

===
If you really want a macro, you may find something close at:

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

or maybe Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm

Excel_nong wrote:

I have a worksheet that contains lists of "action items", that is used to
keep track of jobs that need to be done. I want to be able to automatically
move the contents of a row of cells to another worksheet once the action item
has been completed. In other words, once I have changed the value in a cell
to "yes" (the action has been completed), the information is archived in a
separate worksheet.

Is this possible, and how?

--

Dave Peterson