Usually you would use Autofilter to get the required rows. Then us
specialcells method to copy the visible rows. And finally paste the
rows intot he new worksheet

Sub CopySelection()
'
Set OldSht = Sheets("Sheet1")
Set NewSht = Sheets("Sheet2")

With OldSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

Columns("E:E").AutoFilter
Columns("E:E").AutoFilter Field:=1, Criteria1:="1"

Set SelectedRows = .Rows("2:" &
LastRow).SpecialCells(xlCellTypeVisible)
SelectedRows.Copy _
Destination:=NewSht.Rows(1).Paste
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148648