Of course there is a way.

This is something I put together in a few minutes.. you have to change
it to fit your spreadsheet. I made it so that D5 is where you put the
AutoFilter terms.

Columns("A:A").Select
Selection.AutoFilter Field:=1, Criteria1:=Range("D5").Value
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=546925