Filter in a secondary workbook

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

Code:

---

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

---

I think the With DetailData syntax is incomplete. Precede Sheets with period.

Code:

---

With DetailData
.Sheets("Trans").Range("A5").AutoFilter field = 1, Criteria1:=ACKCode 
End With

---

Or don't use With since it's only one line.

Code:

---

DetailData.Sheets("Trans").Range("A5").AutoFilter field = 1, Criteria1:=ACKCode

---

Consider migrating database functionality to an actual database app, like Access.

You're also missing a colon in the argument:

Code:

---

With DetailData.Sheets("Trans").Range("A5").AutoFilter field:=1, Criteria1:=ACKCode 
End With

---

Thank you June7 and Aflatoon. I see exactly what you both pointed out. Just goes to show when you have been looking at something for a while you can miss the basics. Thank you.