If you're copying data to a different area, it may be more efficient to
use an Advanced Filter. You can record the steps as you do it manually.
There are instructions in Excel's Help, and he

http://www.contextures.com/xladvfilter01.html

wrote:
I need to filter some info from a list, then copy this info to another
area of my speadsheet, i can do this by recording a macro and appying
filters, but is ther an easier way

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html