Filter in a secondary workbook

[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.