VBA AdvancedFilter problem using different workbooks

[JAD0816]

.AdvancedFilter works great if the .range data to filter and the .range criteria list are both within the same workbook.

.AdvancedFilter does not seem to work if the .range data to filter is located in one open workbook, and the .range criteria data to filter is in a different open workbook. For example:

This works (same workbook, different sheets):

ThisWorkbook.Sheets("Sheet1").Range("A1:G25").AdvancedFilter xlFilterInPlace, ThisWorkbook.Sheets("Sheet2").Range("I1:I5")

This also works (same workbook, different sheets):

OtherWorkbook.Worksheets.Sheets("Sheet1").Range("A1:G25").AdvancedFilter xlFilterInPlace, OtherWorkbook.Worksheets.Sheets("Sheet2").Range("I1:I5")

This does not work (different workbooks):

ThisWorkbook.Sheets("Sheet1").Range("A1:G25").AdvancedFilter xlFilterInPlace, OtherWorkbook.Worksheets.Sheets("Sheet2").Range("I1:I5")

Run-time error '1004':
This formula is missing a range reference or a defined name.

Any solutions?

 

[Fluff]

Glad you sorted it & thanks for the feedback.