Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range Based on Cell Content then Copy/Paste to New Sheet,Looping
I have a worksheet that consists of 13 columns and multiple varying
rows of data of 4 to 10 rows set up like the example (column M) starting on row 7. What I would like to accomplish is to select the areas containing 2 on the last row last column up to blank row and paste them to a new sheet looping through entire worksheet. I’ve tried using ActiveCell.CurrentRegion.Select but there are blank columns in some areas. I’ve also looked at xlUp and think it may work but I’ve only been able to select cells not rows. Altogether, I’m having trouble selecting the areas as well as looping through sheet with 264 rows of varying row sizes of data. I’ve looked through previous postings and haven’t seen a similar problem. I hope my example is clear enough. Thanks, Paul Blank Row 1 1 1 1 1 1 Blank Row 1 2 2 2 Blank row |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range Based on Cell Content then Copy/Paste to New Sheet
Use something likje the code below..what copying cells the source and
destination types must be the same. a) Copy range to Range b) Copy row(s) to row c) copy column(s) to column d) Entire Sheet to Entire sheet (select all cells) e) Sheet to sheet (using worksheet name instead of selecting all cells) when copying more the one item as the source only use the first item in the destination, otherwise, if the size are not the same you get weird results. for example copying 2 rows to 3 rows. It is better to copy the 2 rows to the first new row and excel will automatically copy both rows. LastRow = Range("M" & Rows.count).end(xlup).row RowCount = 1 StartRow = RowCount Do While RowCount = LastRow if Range("M" & (MowCount + 1) = "" then set CopyRange = Rows(StartRow & ":" & RowCount) CopyRange.copy destination:=Sheets("Sheet2").Rows(1) StartRow = RowCount + 2 end if RowCount = RowCount + 1 loop "pwk" wrote: I have a worksheet that consists of 13 columns and multiple varying rows of data of 4 to 10 rows set up like the example (column M) starting on row 7. What I would like to accomplish is to select the areas containing 2 on the last row last column up to blank row and paste them to a new sheet looping through entire worksheet. Ive tried using ActiveCell.CurrentRegion.Select but there are blank columns in some areas. Ive also looked at xlUp and think it may work but Ive only been able to select cells not rows. Altogether, Im having trouble selecting the areas as well as looping through sheet with 264 rows of varying row sizes of data. Ive looked through previous postings and havent seen a similar problem. I hope my example is clear enough. Thanks, Paul Blank Row 1 1 1 1 1 1 Blank Row 1 2 2 2 Blank row |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Range Based on Cell Content then Copy/Paste to New Sheet, Looping
areas containing 2 on ??
If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You send a clear explanation of what you want 3. You send before/after examples and expected results. This will find and copy the block of rows. Sub FINDLastValue() Dim fn As String mc = 7 ' Column G On Error GoTo nofind fn = InputBox("Enter the num") lv = Columns(mc).Find(What:=fn, LookAt:=xlWhole, _ SearchDirection:=xlPrevious).Row MsgBox lv ur = Cells(lv, mc).End(xlUp).Row - 1 MsgBox ur Range(Cells(ur, mc), Cells(lv, mc)).EntireRow.Copy Exit Sub nofind: MsgBox "Not found" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "pwk" wrote in message ... I have a worksheet that consists of 13 columns and multiple varying rows of data of 4 to 10 rows set up like the example (column M) starting on row 7. What I would like to accomplish is to select the areas containing 2 on the last row last column up to blank row and paste them to a new sheet looping through entire worksheet. I’ve tried using ActiveCell.CurrentRegion.Select but there are blank columns in some areas. I’ve also looked at xlUp and think it may work but I’ve only been able to select cells not rows. Altogether, I’m having trouble selecting the areas as well as looping through sheet with 264 rows of varying row sizes of data. I’ve looked through previous postings and haven’t seen a similar problem. I hope my example is clear enough. Thanks, Paul Blank Row 1 1 1 1 1 1 Blank Row 1 2 2 2 Blank row |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy paste without selecting sheet or range | Excel Programming | |||
Copy and paste row based on cell content | Excel Programming | |||
based on Cell/Column content ,cut one sheet's values and paste it in other sheet? | Excel Programming | |||
Finding a named range based on cell value and copy/paste to same sheet? | Excel Programming | |||
Copy/Paste based on cell content. | Excel Programming |