It seems that the advanced filter will only work if the sheet has focus. I
have to do something like this.

With oSourceBook.Sheets(QPR_AGENT_SHEET_NAME)
.Range("A1") = "Agent"
.Range("A2") = "ag1"
.Range("A3") = "ag2"
.Range("A4") = "ag3"
.Range("A5") = "ag4"
.Range("A6") = "ag5"
.Range("A7") = "ag6"

' Filter the data
oSourceBook.Activate
.Select
Set oRangeToFilter = .Range("A8:" &
..UsedRange.Cells.SpecialCells(xlCellTypeLastCell) .Address)
oRangeToFilter.AdvancedFilter xlFilterInPlace, Range("A1:A7"), True
End With

I would like to remove the activate and select methods, is it possible?

tia