What range should i do?

Question

Just wondering, why everytime I debug it will appear

Runtime Error 1004; Method 'Range' of object '_Global' failed

and it will be highlight at row

 DataSH.Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("Data!$L$8:$L$9"), CopyToRange:=Range("Data!$N$8:$T$8"), _
            Unique:=False

If the coding is in forms what should the range be? And the range is referred to Master Data sheet. Can anyone help me?

Private Sub cmdContact_Click()
        Dim DataSH As Worksheet
        Set DataSH = Sheet1
        DataSH.Range("L8") = Me.cboHeader.Value
        DataSH.Range("L9") = Me.txtSearch.Text
        DataSH.Range("A2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Data!$L$8:$L$9"), CopyToRange:=Range("Data!$N$8:$T$8"), _
        Unique:=False
        lstEmployee.RowSource = Sheet1.Range("outdata").Address(external:=True)
    End Sub

In the 'Master Data' worksheet's private code sheet all unqualified Range objects belong unconditionally to the 'Master Data' worksheet so `Range("Data!$L$8:$L$9")` means 'give me the cells L8 and L9 on worksheet Data but I want them from the 'Master Data' worksheet'. You cannot find L8 and L9 from worksheet Data in the 'Master Data' worksheet' (it's impossible to resolve) so you get an error. Use `worksheets("Data").Range("L8:L9")` as qualifies where to look for the cells. — , Oct 10 '18 at 01:40
Conversely, in a public module code sheet, a Range object default to the active sheet but are not set in stone and can be overwritten so `Range("Data!L8:L9")` works to reference L8 and L9 from worksheet Data. — , Oct 10 '18 at 01:42
tldr; Using `Range("Data!L8:L9")` style range references are unreliable. Don't use them. — , Oct 10 '18 at 01:44
Oh, so now that's mean if I want L8 and L9 to refer in master data, my code should be written like this -- worksheets(" Master Data").Range("L8:L9"). Am I correct? Can you fix my coding in the answer? So that it will be clear for me. — , Oct 10 '18 at 01:52
I don't believe that AdvancedFilter accepts a CriteriaRange or a CopyToRange from another worksheet and if you wanted criteria or copyto from another worksheet why did you include Data! in the cell reference? — , Oct 10 '18 at 01:56
@Jeeped I think I've used AdvancedFilter with different worksheets before - I threw a quick test together and it worked. — BigBen, Oct 10 '18 at 02:00
I misspoke. The criteria can be on a different worksheet but the copyto range cannot. — , Oct 10 '18 at 02:35
@Jeeped where are you seeing that? I had the data on one sheet and the criteria on another and copied to a third with no problem. — BigBen, Oct 10 '18 at 03:11
@BigBen, I ran an advanced filter in xl2016. When I tried to set another worksheet for the copyto I received [this](https://i.stack.imgur.com/cyO1N.png). Since I rarely rely on ActiveSheet, perhaps that is where the discrepancy comes from. Suffice to say that the copyto range can be another worksheet as long as that worksheet is the activesheet. To me, that doesn't mean you can do it anytime you wish to. — , Oct 10 '18 at 03:22
@jeeped I'm working in Office 365. I used codenames within a simple script and didn't have any immediate error. Testing various data sets, the filtered results were not always consistent and/or what was expected though. Not that it matters that much - I don't use advanced filter often. Was only curious because I could have sworn I used in an answer within the last week or so... — BigBen, Oct 10 '18 at 03:40

Mathieu Guindon · Accepted Answer · 2018-10-10T02:17:42.543

1

Range("Data!$L$8:$L$9")

This unqualified Range call is in fact implicitly qualified with ActiveSheet, via a little bit of default member trickery and a bit of indirection. To keep things simple, we can say that this would be equivalent:

ActiveSheet.Range("Data!__:__")

There's the problem: the address string is qualified with a sheet name, and the Range we mean to get with that string is taken from whatever the ActiveSheet is. If there's a match, we're lucky. Otherwise, boom.

So here we are. Does this work?

ActiveSheet.Range("Data!$L$8:$L$9")

Because this exemplified code throws the same error you're getting, for the same reason:

Sheet1.Range("Sheet2!A1")

edited Oct 10 '18 at 02:17

answered Oct 10 '18 at 02:12

Mathieu Guindon

69,817
8
107
235

If I put ActiveSheet, they will copy the code in the ActiveSheet and not in Master Data. Because from interface i will click on the macro and will key in the data from the user form. – Oct 10 '18 at 02:23
1

In fact, in a worksheet's private code sheet, an unqualified Range implicitly belongs to that worksheet regardless on what is the activesheet. See [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells). – Oct 10 '18 at 02:28

What range should i do?

1 Answers1