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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]File
[/TD]
[TD][/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]test1
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]test2
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]test3
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]test4
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]test5
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]test6
[/TD]
[TD]B2
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.