Array Formula and Paste

[ah2024_2024]

So, I've searched and searched and understand there are so weird things that I need to do when trying to copy and paste an array formula into VBA but man I just cannot get it. I actually have taken the coding out that I did have and now cannot remember even what I had to begin with. But below is what I have for the formula and it should copy the formula that starts in E9 and fill down to the last cell with data in A9.

wstarget.Activate

'getting call data for first day of the month
Range("E9").Select
Selection.FormulaArray = _
"=INDEX('[All Access Report - 2024 06.xlsx]Summary'!R2C[2]:R200C[2],MATCH(1,R6C[-1]='[All Access Report - 2024 06.xlsx]Summary'!R1C[1])*(RC1='[All Access Report - 2024 06.xlsx]Summary'!C2),0)"


Application.ScreenUpdating = True

 

[ah2024_2024]

Here is the formula that goes with the above:
=INDEX('[All Access Report - 2024 06.xlsx]Summary'!G$2:G$200,MATCH(1,D$6='[All Access Report - 2024 06.xlsx]Summary'!F$1)*($A9='[All Access Report - 2024 06.xlsx]Summary'!$B:$B),0)

 

[Alex Blakenburg]

Step 1 before getting your formula to work in VBA is to make sure it works in Excel.
I don't believe your formula works.
Leaving out the workbook reference I think its most likely meant to be:

=INDEX(Summary!G$2:G$200,MATCH(1, ( D$6=Summary!F$1 )*($A9=Summary!$B2:$B200),0))

The RC format has some additional issues

Assuming the above to be the case, try this:

Sub InsertFormula()

    Dim ws As Worksheet
    Dim sFormula As String
    Dim LastRow As Long
    
    Set ws = ActiveSheet
    With ws
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    End With
    
    sFormula = "=INDEX('[All Access Report - 2024 06.xlsx]Summary'!R2C[2]:R200C[2],MATCH(1,(R6C[-1]='[All Access Report - 2024 06.xlsx]Summary'!R1C[1])*(RC1='[All Access Report - 2024 06.xlsx]Summary'!R2C2:R200C2),0))"
    ws.Range("E9:E" & LastRow).Formula2R1C1 = sFormula

End Sub