excelvbaex

Unable to set excel VBA worksheet variable

I am trying to set a source worksheet variable to a target worksheet variable in another workbook(DataSource1.xlsx) with the purpose of eventually using it to read the target worksheet and perform some calculation but it gives me the subscript out of range error.

This is my code

Sub GenerateReport()

Dim source As Worksheet

Dim path As String    
Set source= Workbooks(ThisWorkbook.path & "\DataSource1.xlsx").Sheets("Sheet1")

'path = ThisWorkbook.path

'Set wb = Workbooks.Open(ThisWorkbook.path & "\DataSource1.xlsx")

Dim one As Integer

one = 10

End Sub

The file are all placed in the same folder and I have checked that there are no naming or path errors, why is this happening?

EDIT

This is what my project looks like after GSerg comments

enter image description here

Solution

  • If the workbook is already opened, you need to refer to it by name without path:

    Set source = Workbooks("DataSource1.xlsx").Sheets("Sheet1")

    If the workbook is not opened yet, you need to open it first:

    Set source = Workbooks.Open(ThisWorkbook.path & "\DataSource1.xlsx").Sheets("Sheet1")