There may be a *much* easier way than this, but it depends on what the
ranges of non-contiguous cells are. IF they are all made up of the same
columns, then you can just Copy them. Here are two examples...

Entire Rows
======================
Set Source = Range("1:4,8:13,20:20,22:25")
Set Destination = Worksheets("Sheet4").Range("A2")
With Source
.Copy Destination.Range("A4").Resize(.Rows.Count, .Columns.Count)
End With

Partial Rows
======================
Set Source = Range("A3:G5,A9:G16,A20:G30")
Set Destination = Worksheets("Sheet4").Range("A2")
With Source
.Copy Destination.Range("A4").Resize(.Rows.Count, .Columns.Count)
End With

Note: For both conditions, the Destination range is a single cell...
the first cell that will be copied to on the destination sheet.

--
Rick (MVP - Excel)


"joel" wrote in message
...

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