Dim sh1 as Worksheet, sh2 as Worksheet
set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")

sh1.range("A1:A100").SpecialCells( _
xlConstants).EntireRow.copy Destination:=sh2.cells( _
rows.count,1).End(xlup)(2)

Assuming rows to copy have a constant value in column A else they are blank.

--
Regards,
Tom Ogilvy


"enyaw" wrote:

Thanks Tom

Now i need to know if there is a way of copying information and not copying
blank rows.

"Tom Ogilvy" wrote:

Dim sh1 as Worksheet, sh2 as Worksheet
set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")

sh1.range("A1:A10").copy Destination:=sh2.cells( _
rows.count,1).End(xlup)(2)

--
Regards,
Tom Ogilvy


"enyaw" wrote:

This seems to work but it will overwrite anything i copy over if i do more
than one copy. It will not pick the next empty cell after I copy something
over.

" wrote:

various methods, but the one I use the most would be
sheets("whatever").range("A1").offset(WorksheetFun ction.CountA(Range("A:A"))-1,0).value=sheets("source").range("source").value


In theory you can use the special cells to get XLLastCell but I've had
problems with this on some users machines, so a counta gets round it!