Application-defined or object-defined error when copying formulas

[mjlh]

Hi,
I'm trying to merge multiple worksheets from different workbooks into 1, and i want to copy and paste a formula into the cell after converting it into a table. However, when I tried to insert the required formula, it keeps giving me the Application-defined or Object-Defined error. Am I missing a step or should this be done earlier in the code?

This is the code line that gets highlighted when I debug:

mWB.Sheets("STaR Data 2020").Range("Detailed[9-box Grade]").FormulaR1C1 = "=IFERROR(INDEX('Potential Assessment'!R5C4:R14C16384,10,MATCH(RC[-18],'Potential Assessment'!R6C4:R6C16384,0)),"")"

The full code is as follows:

Sub ImportSTaRData()
     'Uses methods found in http://vbaexpress.com/kb/getarticle.php?kb_id=151 and
     ' http://vbaexpress.com/kb/getarticle.php?kb_id=221
     
    Dim Path            As String 'string variable to hold the path to look through
    Dim FileName        As String 'temporary filename string variable
    Dim tWB             As Workbook 'temporary workbook (each in directory)
    Dim tWS             As Worksheet 'temporary worksheet variable
    Dim mWB             As Workbook 'master workbook
    Dim aWS             As Worksheet 'active sheet in master workbook
    Dim RowCount        As Long 'Rows used on master sheet
    Dim uRange          As Range 'usedrange for each temporary sheet
    Dim LastRowUsed     As Long
    Dim destrange       As Range
    Dim calcmode        As Long
    
    
    
     '***** Set folder to cycle through *****
 
    Application.ScreenUpdating = False
 
 
 With Application.FileDialog(msoFileDialogFolderPicker) 'Dialogue to select folder wtih files
      .AllowMultiSelect = False
      .Show
      On Error Resume Next
      Path = .SelectedItems(1)
      Err.Clear
      On Error GoTo 0
    End With
 
 Application.EnableEvents = False 'turn off events
    Application.ScreenUpdating = False 'turn off screen updating
    Set mWB = ActiveWorkbook 'select workbook variable as open file
    mWB.Sheets("STaR Data 2020").Select 'select worksheet to copy data into
    Set aWS = mWB.ActiveSheet 'set active sheet variable to data sheet
    LastRowUsed = aWS.Cells(aWS.Rows.Count, "C").End(xlUp).Row
 
 
    If Right(Path, 1) <> Application.PathSeparator Then 'if path doesnt end in "\"
        Path = Path & Application.PathSeparator 'add "\"
    
    FileName = dir(Path & "*.xl*", vbNormal) 'set first file's name to filename variable
    Do Until FileName = "" 'loop until all files have been parsed
        If Path <> ThisWorkbook.Path Or FileName <> ThisWorkbook.Name Then
            Set tWB = Workbooks.Open(FileName:=Path & FileName) 'open file, set to tWB variable
             ActiveWorkbook.Unprotect Password:="star2020"
            Set tWS = tWB.Sheets("STaR Data 2020")
                Set uRange = tWS.Range("C3:BG10") 'set used range
                If RowCount + uRange.Rows.Count > 65536 Then 'if the used range wont fit on the sheet
                    aWS.Columns.AutoFit 'autofit mostly-used worksheet's columns
                    Set aWS = mWB.Sheets.Add(After:=aWS) 'add a new sheet that will accommodate data
                    RowCount = 0 'reset RowCount variable
                End If
             
               
             Set destrange = aWS.Range("C" & RowCount + LastRowUsed + 2)
            
             
            uRange.Copy
With destrange
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
End With
             
                       
        RowCount = RowCount + uRange.Rows.Count 'increase rowcount accordingly
        tWB.Close False 'close temporary workbook without saving
        End If
        FileName = dir() 'set next file's name to FileName variable
    Loop
   mWB.Sheets("STaR Data 2020").Select 'select fourth data sheet on master workbook
     'aWS.Protect ("9a9b9c")
     Application.EnableEvents = True 're-enable events
    Application.ScreenUpdating = True 'turn screen updating back on
     

mWB.Sheets("STaR Data 2020").Range("C2:BG1000").AutoFilter field:=11, Criteria1:=""
Application.DisplayAlerts = False
mWB.Sheets("STaR Data 2020").Range("C3:BG1000").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True

On Error Resume Next
mWB.Sheets("STaR Data 2020").ShowAllData
On Error GoTo 0

 ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A2:BG1000"), , xlYes).Name = _
        "Detailed"
    ActiveSheet.ListObjects("Detailed").TableStyle = "TableStyleLight15"
    ActiveSheet.ListObjects("Detailed").ShowTableStyleRowStripes = False

mWB.Sheets("STaR Data 2020").Range("Detailed[9-box Grade]").FormulaR1C1 = "=IFERROR(INDEX('Potential Assessment'!R5C4:R14C16384,10,MATCH(RC[-18],'Potential Assessment'!R6C4:R6C16384,0)),"")"

ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
     .Calculation = xlAutomatic
     

    End With
    
       
      End If
      

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
You need to double-up on the quotes " inside the formula, so the end of the formula should be

)),"""")"

 

[mjlh]

Hi Fluff,
That worked perfectly thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.