Advanced Filter VBA

[lwarren30]

Hello,
I'm using an advanced filter vba to search for data from within a large data set onto another sheet. I've worked this before but this time it is not working.

Range("Sheet_K_2D_FinancialInput").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheet15.Range("BV4:BZ5"), CopyToRange:=Sheet15.Range("AV5:BS5"), Unique:=False

I can provide screenshots if you are familiar with this code.

 

[Kenneth Hobson]

It can only copy to the same sheet. Use AutoFilter....

 

[Akuini]

Try (untested):

With Range("Sheet_K_2D_FinancialInput")
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheet15.Range("BV4:BZ5"), Unique:=False
    .SpecialCells(xlCellTypeVisible).Copy Sheet15.Range("AV5")
End With

 

[Kenneth Hobson]

The advanced filter CopyTo range must be relative, the activesheet. The other two inputs can be on another sheet.

 

[lwarren30]

Run-time error '1004':
Extract range is not defined.
On line 2.

 

[Akuini]

Maybe there's a problem with the named range?
Let's try just filtering in place (just to test the named range), is it work?

Range("Sheet_K_2D_FinancialInput").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheet15.Range("BV4:BZ5"), Unique:=False

 

[Akuini]

Or a simpler test:

Debug.Print Range("Sheet_K_2D_FinancialInput").Address

 

[Kenneth Hobson]

Sheet15.Activate
Range("Sheet_K_2D_FinancialInput").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("BV4:BZ5"), CopyToRange:=Range("AV5:BS5"), Unique:=False

 

[lwarren30]

It filters in place with:

Range("Sheet_K_2D_FinancialInput").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheet12.Range("AA2:AE3"), Unique:=True

Maybe there's a problem with the named range?
Let's try just filtering in place (just to test the named range), is it work?

Range("Sheet_K_2D_FinancialInput").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheet15.Range("BV4:BZ5"), Unique:=False

 

[lwarren30]

Still no luck when i try to have it copy to Sheet15.