Search code examples
excelvbacopycopy-paste

Copy a range in a workbook and paste it in my current/open workbook

I would like to copy some data in a workbook (which will always have the same name: NEW LATEST Loanbook) and copy it in the current workbook I am using. This is the code I have for now:

I have an error at line 3: Workbooks.Open The file I am trying to open is an xlsm file.

Do you have any idea how I can paste it correctly to the other workbook I am using in the correct sheet ?


Set x = Workbooks.Open("G:\Shared drives\Reporting\Power BI Source Files- DO NOT TOUCH\Loanbook\LATEST NEW Loanbook")
Workbooks.Open("G:\Shared drives\Reporting\Power BI Source Files- DO NOT TOUCH\Loanbook\LATEST NEW Loanbook").Activate
x.Sheets("Payment Holidays").Range("A1:G55").Select
Selection.Copy
Set y = ThisWorkbook
ThisWorkbook.Activate
ThisWorkbook.Sheets("RAW Payment Holidays").Range("A1:G55").PasteSpecial Paste:=xlPasteValues

End Sub
Solution

  • 1st line of your code: Do not forget to mention the files ending (e.g. .xlsm, .xls or .xlsx). This might be one reason for the error.

    2nd line of your code: Once you have declared your workbook as x, you can refer to it by using its name. This is no reason for your error, but increases the readability of your code.

    x.Activate

    3rd & 4th line of your code: Try to avoid using select. You can write this in one lane instead. This is no reason for your error, but increases the reliability of your code.

    x.Sheets("Payment Holidays").Range("A1:G55").Copy

    5th line of your code. You want y to be ThisWorkbook. As you've currently activated sheet x , this will set y also to your spreadsheet NEW LATEST Loanbook, even though you most likely want to refer to the workbook that you have open at the beginning of the macro. Consider writing this line before opening the other workbook to avoid errors. This most likely caused your error.

    6th & 7th line of your code. See comment of the 2nd and the 5th line. As you want to call the sheet you opened first (not the latest one) and you already declared it as "y", you can refer to it as y.

    y.Activate
y.Sheets("RAW Payment Holidays").Range("A1:G55").PasteSpecial Paste:=xlPasteValues

    Thus, your total code should like like that one below. Please check if it works.

    Sub xxx()

Set y = ThisWorkbook
Set x = Workbooks.Open("G:\Shared drives\Reporting\Power BI Source Files- DO NOT TOUCH\Loanbook\LATEST NEW Loanbook.xlsm") 

x.Activate
x.Sheets("Payment Holidays").Range("A1:G55").Copy

y.Activate
y.Sheets("RAW Payment Holidays").Range("A1:G55").PasteSpecial Paste:=xlPasteValues

End Sub