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!

If i have any formulas in the cells the values are not copied and pasted
over. Is there a way of copying and pasting over these values?

"Tom Ogilvy" wrote:

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.

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

for each cell in sh1.range("A1:A100")
if cell.Value < "" then
cell.EntireRow.copy Destination:=sh2.cells( _
rows.count,1).End(xlup)(2)
end if
Next

--
Regards,
Tom Ogilvy


"enyaw" wrote:

If i have any formulas in the cells the values are not copied and pasted
over. Is there a way of copying and pasting over these values?

"Tom Ogilvy" wrote:

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!