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

Question

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

Answer 1

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

Answer 2

First, following on from my comment. When opening an Excel file, it's mandatory to specify to the file type i.e. the file extension, in your case .xlsm.

Second, when working with multiple workbooks, it's good practice to set clear workbook objects so it's easy for you to distinguish between them.

Third, in your operation there is no need for you to use copy and paste. Traditionally, in VBA, you use the .Copy method as a last resort. In your case you can simply set the ranges to each other.

See below code.

Option Explicit

Sub Set_Range()
    
    Dim wbNEW As Workbook
    Dim wbCurrent As Workbook
    Dim sFilePath As String, sFileName As String
    
    sFilePath = "G:\Shared drives\Reporting\Power BI Source Files- DO NOT TOUCH\Loanbook\"
    'always include the file extension
    sFileName = "LATEST NEW Loanbook.xlsm"
    
    'two clear names
    Set wbNEW = Workbooks.Open(sFilePath & sFileName)
    Set wbCurrent = ThisWorkbook
    
    'set values
    wbCurrent.Sheets("RAW Payment Holidays").Range("A1:G55").Value = wbNEW.Sheets("Payment Holidays").Range("A1:G55").Value
    
End Sub

Answer 3

You should add the file extension and you don't actually need to select anything or activate any workbook or worksheets. Try this adjusted code and see it works.

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