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