Copy and paste under the last used row - what's wrong with this code?

Hi,

I have a macro recording some production data. Everything works fine except the last action. I want to copy all data from active sheet (row 31 until last row in column M), then open another sheet (database) and paste data under the last row. Everything works, but the data always paste under the last cell in column "A" and rewrite data in other columns under this row (cells in column "A" are not always having data in them). That makes sense, because I'm using the "A1000000" as Range in my code. But I have tried everything to make it work, based on the last cell/row used with no success. Ideally, I would like to find the last used row within columns A:M and paste the data underneath. Some of the columns have date or name (not sure if that makes any difference). The copy and opening+closing works fine, it's just the paste location. I will be really grateful for any suggestions. Thanks

Here is the code:


Application.ScreenUpdating = False
Application.CopyObjectsWithCells = False

Dim Rowcount As Integer
Dim DataRowCount As Integer

Set Obook = ActiveWorkbook
Set DBook = Workbooks.Open("K:\EXCEL\SPC\Mastersheet SPC.xlsx")
With Obook.ActiveSheet
LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
.Rows("31:" & LastRow).Copy

DBook.ActiveSheet.Range("A1000000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = 0


End With

DBook.Save
DBook.Close

Luke,
Please use code tags with your code as per forum's rule.

Please Login or Register  to view this content.

Hi,

Thanks a lot for quick reply. Unfortunately, this code still rewrites some of the previous entries (not pasting under the last used cell) and also for some reason it pasted the values from row 32, leaving the first 31 rows blank. Any idea why it is doing that? Thanks

I have tested it on my sample and it works. Could be for 2 reasons.

Why are you pasting in active sheet? Does DBook have a sheet name rather than active sheet?

You must have data above row 31. The code finds the next empty row in Columns A-M in row 32 because the next empty cell is row 32.

You could hard coded, but it will overwrite into existing data again if you re-run the code.