How do i copy information from one sheet and paste it into another sheet at
the next available empty cell?

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!

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!

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!

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!

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!

The range A:A needs to be set to wherever you are putting data, and it
will ONLY work if there are no blank rows in the data.