I have 2 files;
1st - Forecast Review file which reports month by month spend by account code. The account code is in column A.
2nd - Detailed Transaction list for each account code.

On selecting the cell in column A (for the account code I would like to review) of the Forecast Review file I am then trying to filter in a second workbook that is open so I can see the detail transaction behind the account code within the selected cell.

Both files are stored in the same location: C:\MyDrive\Month Forecasts\
Main file is: Forecast Workings.xlsm (sheet name = YTD Review)
Second file is: Subledger Transactions.xlsx (sheet name = Trans)

I believe I am using Microsoft 365 apps for enterprise.

The macro is in the main file and I am using a selectionchange event to trigger.

It fails at the following line:
Sheets("Trans").Range("A5").AutoFilter field = 1, Criteria1:=ACKCode


The error I get is Run-time error ‘1004’: Autofilter method of Range class failed


It's been a while since I have compiled code so help is much appreciated. I am assuming this is something to do with the code being in one workbook and the filter action in a second and I am not doing this right.

Thank you
Jay

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Intersect(Target, Range("A10:A5000")) Is Nothing Then
      Exit Sub
   Else
      Call FilterReviewDTR
   End If
End Sub

Sub FilterReviewDTR()

Dim fileName As String
fileName = "C:\MyDrive\Month Forecasts\Subledger Transactions.xlsx"


'Call function to check if the file is open
    If IsFileOpen(fileName) = False Then
        MsgBox "The Review DTR is not open - if you want to review transactions open the file and re-select the cell"
    Else
        ACKCode = Selection.Value


Set DetailData = Workbooks.Open("C:\MyDrive\Month Forcasts\Subledger Transactions.xlsx")
    
With DetailData
Sheets("Trans").Range("A5").AutoFilter field = 1, Criteria1:=ACKCode  
End With
    
End If
End sub