Search code examples
excelexcel-2007vba

Copy to last non-empty cell in a row

I want to copy data from a specific range to last active cell in a specific row

Right now I use this code:

wrkbook1.Sheets("sheet1").Range("A" & Cells.Rows.Count).End(xlUp).Copy

It only copies the last active cell

Solution

  • This is too many lines to put into a comment because you have to make sure that the Cells inside the Range is from the same worksheet.

    'copy from all of sheet1 columns A
with wrkbook1.Sheets("sheet1")
    .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy
end with

'paste to next blank row in sheet2 column A
with wrkbook1.Sheets("sheet2")
    .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
end with