Search code examples
excelvbarangecopy-paste

How to copy a dynamic range of cell from another workbook, paste it to another one?

I need to update my main file every time a third party sends me an updated version of his input. Therefore, I need to copy-paste the range of this new input in a saved workbook on my computer. The range needs to include all columns and all rows if the value in column A is greater than 0.For example, in the picture below, from A1 to A45.

enter image description here

I found a way to select the rows and stop at the first zero. I've put a sumprodcut formula on the side that I call in my code i.

For now, I have this code:

I have an error on line wb1.Sheets("Accounts_latest").Range("A1:BW & i").Copy, I can't fix it... do you have any idea?

Let me know :)

Antoine 

Sub CopyPaste()

    Dim wb1 As Workbook
    Dim wb2 As Workbook

    'Open Workbook from Pepper

    Set wb1 = Workbooks.Open("G:\Shared drives\Reporting\Power BI Source Files- DO NOT TOUCH\Pepper Automation\Accounts latest\Accounts updated\Accounts_latest.xlsx")

    'Copy Range (Column A to BW - all filled rows)
    Dim i As Integer
    i = Sheets("Accounts_latest").Range("CA1").Value
    wb1.Sheets("Accounts_latest").Range("A1:BW & i").Copy

    'Paste to worksheet in workbook2:
    Set wb2 = Workbooks("20200403 Selina - Loanbook V2.09 (1).xls")
    wb2.Activate
    wb2.Sheets("Pepper Accounts RAW").Range("A1:BW").PasteSpecial Paste:=xlPasteValues
    Range("A1").Select

    'Close workbook
    wb1.Close savechanges:=True
    Application.DisplayAlerts = True

End Sub
Solution

  • try this:

     wb1.Sheets("Accounts_latest").Range("A1:BW" & i).Copy

    or

     wb1.Sheets("Accounts_latest").Range("A1","BW" & i).Copy

    And

    wb2.Sheets("Pepper Accounts RAW").Range("A1").PasteSpecial Paste:=xlPasteValues

    Or

    wb2.Sheets("Pepper Accounts RAW").Range("A1:BW" & i).PasteSpecial Paste:=xlPasteValues

    or

    wb2.Sheets("Pepper Accounts RAW").Range("A1").Resize(i,1).PasteSpecial Paste:=xlPasteValues