Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP copy non-continous range
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP copy non-continous range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP copy non-continous range
Marina,
I think we need additional detail about what your trying to copy and to where. Mike "Marina" wrote: 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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP copy non-continous range
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of continous cell | Excel Worksheet Functions | |||
continous assessment help with vba | Excel Programming | |||
Compare 2 list and extract continous range | Excel Programming | |||
deleting rows in a non-continous range | Excel Programming | |||
deleting rows in a non-continous range | Excel Programming |