VBA - copying column data from one workbook to the other and pasting in the last cell of a column in the new workbook

[tanyaleblanc]

Hi, I'm trying to copy column of cells from on column from one workbook to another column on another workbook and paste it in the last blank cell of that column in the new workbook, below, all the data should be pasting in the last cell of each column, most are pasting correctly but some are not. They should paste in cell 50 - 55 of my columns, but some are pasting in cells 56- 61

Windows("Rental Extract.xls").Activate    Range("Q11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    Range("J" & lrownew).PasteSpecial Paste:=xlPasteValues
    Windows("Rental Extract.xls").Activate
    Range("R11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    Range("M" & lrownew).PasteSpecial Paste:=xlPasteValues
    Windows("Rental Extract.xls").Activate
    Range("N11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    Range("N" & lrownew).PasteSpecial Paste:=xlPasteValues
    Windows("Rental Extract.xls").Activate
    lRow = Cells(Rows.Count, "E").End(xlUp).Row
    Range("C11:C" & lRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    lrownew = Cells(Rows.Count, "E").End(xlUp).Row + 1
    Range("O" & lrownew).PasteSpecial Paste:=xlPasteValues
[B]    'pasted in row 56-61, column O but should have been 50-55, column O[/B]
    Windows("Rental Extract.xls").Activate
    lRow = Cells(Rows.Count, "E").End(xlUp).Row
    Range("B11:B" & lRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    lrownew = Cells(Rows.Count, "E").End(xlUp).Row + 1
    Range("P" & lrownew).PasteSpecial Paste:=xlPasteValues
    Windows("Rental Extract.xls").Activate
    Range("A11:A" & lRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
[B]'same error as above[/B]
    Windows("New Rental Contract.xls").Activate
    Range("W" & lrownew).PasteSpecial Paste:=xlPasteValues
    Windows("Rental Extract.xls").Activate
    Range("W11:W" & lRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    Range("Q" & lrownew).PasteSpecial Paste:=xlPasteValues
    [B]'same error as above[/B]
    Windows("Rental Extract.xls").Activate
    Range("X11:X" & lRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    Range("R" & lrownew).PasteSpecial Paste:=xlPasteValues
    [B]'same error as above[/B]
    Windows("Rental Extract.xls").Activate
    Range("Y11:Y" & lRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    Range("U" & lrownew).PasteSpecial Paste:=xlPasteValues
   [B]'same error as above[/B]
    Windows("Rental Extract.xls").Activate
    Range("Z11:Z" & lRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New Rental Contract.xls").Activate
    Range("V" & lrownew).PasteSpecial Paste:=xlPasteValues
    [B]'same error as above[/B]

 

[mumps]

I don't see anywhere in your macro how the variable lrownew is calculated. Also, you don't need to use "Activate" or "Select" to do perform most actions. I think that it would be easier to help and test possible solutions if I could work with your actual files which includes any macros you are currently using. Perhaps you could upload a copy of your 2 files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.