HELP copy non-continous range

M

Marina

How do you copy non-continous range of cells and paste them into the
first cells of another new sheet for every row that is being picked up
by the IF statement?
 
J

joel

Usually you would use Autofilter to get the required rows. Then u
specialcells method to copy the visible rows. And finally paste th
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 Su
 
M

Mike H

Marina,

I think we need additional detail about what your trying to copy and to where.

Mike
 
R

Rick Rothstein

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.
 
Top