Vlookup with sheet names stored in array

[Syaza Azman]

Hi,
i'm trying to vlookup some comments from a different workbook with its sheets names stored in array.

i have two problems here.

1. The first vlookup runs without an error, but i didnt get any results
2. The second vlookup gives error 1004 ; apllication-defined or object-defined error

For Each TabName In Array("report", "Pending", "Created")
        
        On Error GoTo NextTabName8
                      
            If TabName = "report" Then
            
                VLatCell = "T3"
                AutoFillIndicator = "R3"
                
                Windows("Work_Order_Report__Weekly_Repo.xlsx").Activate
                Sheets(TabName).Select
                Range("T1").Value = TabName
                Range(VLatCell).Select
                ActiveCell.FormulaR1C1 = _
                    "=VLOOKUP(RC[-5],'[Work Order Report - 8 Week.xlsx]" & "TabName" & "'!C15:C20,6,0)"
                    
                Range(AutoFillIndicator).Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 2).Select
                Range(Selection, "T3").Select
                Selection.FillDown
            
            Else
            
            
                VLatCell = "S2"
                AutoFillIndicator = "R2"
                
                        
                Windows("Work_Order_Report__Weekly_Repo.xlsx").Activate
                Sheets(TabName).Select
                Range(VLatCell).Select
                ActiveCell.FormulaR1C1 = _
                    "=VLOOKUP(RC[-4],'[Work Order Report - 8 Week.xlsx]" & "TabName" & "'!C15:C19,5,0)"
                    
                Range(AutoFillIndicator).Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 2).Select
                Range(Selection, "S2").Select
                Selection.FillDown
        
            End If


NextTabName8:
Next TabName