Copy data to another sheet last row

[yayakele]

Hi...I want to copy data from one sheet,ie "input data" and paste them on another sheet, ie "data" , i need to paste to the last row in that datasheet. the source data range is varied, sometime like range B6:N6, sometimes like range B6:N20. How do i write the code to paste the selection to the blank row under exist data, defined by column B? can i make a loop formula so the source data can copy and paste automatically without determine the range to copy and paste

 

[Joe4]

Which image is of which sheet? The second image has no row numbers and limited columns shown, so it is hard to follow exactly what ranges you are trying to copy where.

If you wanted to copy from cell B6 to the last row and copy over to column N, then assuming column B always has an entry for every row with data, you can dynamically find the last row like this:

Dim lr as Long
lr = Sheets("input data").Cells(Rows.Count, "B")

so then you could copy that range dynamically like this:

Sheets("input data").Range("B6:N" & lr).Copy

Likewise, you can find the last populated column in a row on your destination sheet. So if we were to look at column B on your "data" sheet to find the last row with data in that column, it would look like this:

Dim lr2 as Long
lr2 = Sheets("data").Cells(Rows.Count, "B")

Then, when we paste, simply add one to that row number to past on the next line, i.e.

Sheets("data").Activate
Range("B" + lr2 + 1).Select
ActiveSheet.Paste

Hope that helps!
Let us know if you have any questions.