Run time error with Advance Filter in VBA

[ThrottleWorks]

Hi,

I am trying to do an advance filter with macro.

I am getting error as Method ‘Range’ of object’ _Global’ failed while running advanced filter while running below mentioned line.

Range("Coverage").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("K1:L2"), CopyToRange:=Range("O1:W1"), Unique:=False

Surprising part is, I have copied this macro file from folder A to folder B.

In original file at folder A, this macro is running fine but when I try to run this macro from macro file in folder B, I am getting error.

Since, I have made multiple changes in other modules of the latest file, cannot revert to original file.
I copied the entire module from previous file, pasted this module in new file, even after that I am getting same error.

Even the raw data that is being used for advanced filter is same.
The sheet name where I am pasting data to run advance filer is same.

At original file, if I check range address of Range("Coverage") in IM window I am getting valid result.
However in updated file, I am not getting valid result for range in IM window.

Also, I do not know, how does this Range("Coverage") works, cause I do not have any worksheet named as "Coverage"

Can anyone please help me in this.

 

[Marc L]

Hi !

As Range can't be a worksheet …​

 

[Chihiro]

Couple of things I can think of.

1. Make sure Named Range scope is set to Workbook
2. Make sure that the code is run while the sheet containing Criteria and Output range is active.

Otherwise, modify your code to reference Worksheets("Name/Index").Range instead of just range.

 

[Chihiro]

Here's sample workbook where your code has been applied and functioning.

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help. Done 2 changes in my code.
Selected the sheet before running the code.

DummySht.Select
    Dim AdFiltRng As Range
    Set AdFiltRng = DummySht.Range("A1:I65000")
  
    AdFiltRng.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("K1:L2"), CopyToRange:=Range("O1:W1"), Unique:=False

This seems to be working without error. Could you please guide if this is the right way.
Have a nice day ahead.

 

[ThrottleWorks]

Hi @Chihiro sir, my doubt is what does 'Coverage' defines in the code.

In the code you have provided, first line is Range("Coverage").
I am confused about this line. Can you please help if you get time.

Sub test()
Range("Coverage").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("K1:L2"), CopyToRange:=Range("O1:W1"), Unique:=False
End Sub

 

[Chihiro]

So "Coverage" is named range. If you go to formula tab in the workbook and check, you will see that "Coverage" is set for Workbook and defined as range Sheet1!$A$1:$I$29.



Your modified code is fine. But rather than selecting the sheet, I would personally change CriteriaRange and CopyToRange to have sheet reference as well.

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot ! Now I got it. The original file has a named range as 'Coverage', latest file has lost this range due to some reasons.

Main problem was, I did not realize that a named range is being used.
I got confused between sheet name.

Though I know what named ranges are, totally forgot it today.