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]

Hi & welcome to MrExcel.
Try

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

 

[JAD0816]

Thank you for the response. Yes, my post above is incorrect:

OtherWorkbook.Worksheets.Sheets("Sheet2").Range("I1:I5")
should be:
OtherWorkbook.Worksheets("Sheet2").Range("I1:I5")
or:
OtherWorkbook.Sheets("Sheet2").Range("I1:I5").
I cannot get either to work.

The range counts come out fine:
Debug.Print Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("Sheet1").Range("A1:G25"))
Debug.Print Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("Sheet2").Range("I1:I5"))
Debug.Print Application.WorksheetFunction.CountA(OtherWorkbook.Worksheets("Sheet1").Range("A1:G25"))
Debug.Print Application.WorksheetFunction.CountA(OtherWorkbook.Worksheets("Sheet2").Range("I1:I5"))

Just wondering if this is by design?
Does the .range data to filter and the .range criteria list have to be within the same workbook for .AdvancedFilter to work?

 

[Fluff]

Does the .range data to filter and the .range criteria list have to be within the same workbook for .AdvancedFilter to work?

Nope, it worked fine for me having the criteria in another open workbook.

 

[RoryA]

Works fine for me, so there must be something else wrong. I suggest you post the full code, and double-check all the sheet names and the ranges.

 

[JAD0816]

Hey that's great! Thanks for the response. I've given up on trying to figure this out thinking it must be by design. I'll dig back into my code and strip it down to an example that can be posted.

 

[JAD0816]

Test 1: Filtering from two different open workbooks works great:

Public Sub TestCode_1()

    Dim UserWB As Workbook
    Set UserWB = Workbooks("CodeTest.xlsm")
    
    ThisWorkbook.Sheets("Sheet1").Range("B3:T33").AdvancedFilter xlFilterInPlace, UserWB.Sheets("Sheet2").Range("A3:A4")[/ICODE]

End Sub

Test 2: If the 2nd workbook is not already open, and instead the 2nd workbook is opened through code, and the same Test 1 filter is run, there is an error.

[CODE]Public Sub TestCode_2()

    Dim UserApp As Application
    Set UserApp = New Application
    UserApp.Visible = True
    
    Dim UserWB As Workbook
    Set UserWB = UserApp.Workbooks.Open("C:\Users\jad08\Documents\CodeTest.xlsm", True) 'true for update links
          
    ThisWorkbook.Sheets("Sheet1").Range("B3:T33").AdvancedFilter xlFilterInPlace, UserWB.Sheets("Sheet2").Range("A3:A4")

End Sub

Test 3: Run the same test 2 code, but change the filter to only the 2nd workbook, works great:

Public Sub TestCode_3()

    Dim UserApp As Application
    Set UserApp = New Application
    UserApp.Visible = True
    
    Dim UserWB As Workbook
    Set UserWB = UserApp.Workbooks.Open("C:\Users\jad08\Documents\CodeTest.xlsm", True) 'true for update links
          
    UserWB.Sheets("Sheet1").Range("B3:T33").AdvancedFilter xlFilterInPlace, UserWB.Sheets("Sheet2").Range("A3:A4")

End Sub

Any help would be greatly appreciated, thank you.

 

[Fluff]

Pretty sure that both workbooks need to be open in the same instance of Excel.

 

[JAD0816]

Thanks Fluff, but I'm confused.
What's the difference between 2 workbooks being opened manually prior to running the filter, versus, 1 workbook being opened manually, then through code opening the 2nd workbook, then running the filter?

Pretty sure that both workbooks need to be open in the same instance of Excel.

Thanks Fluff, but I'm confused.
What's the difference between 2 workbooks being opened manually prior to running the filter, versus, 1 workbook being opened manually, then through code opening the 2nd workbook, then running the filter?

 

[JAD0816]

I figured out the issue. It's in the creation of a new application. If I skip this part, and take all reference to UserApp out of the code it works.