Pulling data from another workbook with VBA code.

[andrelu78]

Hello! I am trying to pull data from another workbooks with the INDIRECT function. I have the following table in the main workbook:

File		Value
test1	B2
test2	B2
test3	B2
test4	B2
test5	B2
test6	B2

<tbody>
</tbody>

I am using this VBA code based on another posted in this forum:

Sub Open_Workbooks_and_Create_Formulas2()
    Dim r As Long
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.ActiveSheet
        
        For r = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
            Workbooks.Open ThisWorkbook.Path & "\" & Cells(r, "A") & ".xlsx"
            
            '=INDIRECT("'C:\Users\a278078\Desktop\["&TEXT(A2")&".xlsx]Sheet1'!"&B2)
            
         .Cells(r, "C").ClearContents
         .Cells(r, "C").Formula = "=INDIRECT(""'" & ThisWorkbook.Path & "\[" & "A" & r & ".xlsx]Sheet1'!""&B" & r & ")"
         
        Next
        
        .Activate
        
    End With
            
    Application.ScreenUpdating = True
    
End Sub

What this does is open the files listed in the collum A and uses the INDIRECT function to pull data specified in the collum B to collum C.

The problem is that when trying to open the secound file, the code is reading the cell in the first file that was opened, not the main workbook.