Filter in a secondary workbook

[JayBee2012]

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

[June7]

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

With DetailData
.Sheets("Trans").Range("A5").AutoFilter field = 1, Criteria1:=ACKCode
End With
Or don't use With since it's only one line.

DetailData.Sheets("Trans").Range("A5").AutoFilter field = 1, Criteria1:=ACKCode
Consider migrating database functionality to an actual database app, like Access.

[Aflatoon]

You're also missing a colon in the argument:


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

[JayBee2012]

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.