What is wrong with my advanced filter code?

[bananajelly]

I'm trying to filter out unique values. I can't figure out what is wrong with my code. It is an application-defined or object-defined error. What am I defining wrong?

Dim MixRecipes, RawIngList As Worksheet
Set MixRecipes = Worksheets("Sheet1")
Set RawIngList = Worksheets("Sheet2")

Dim MixIDFilter As Range
Set MixIDFilter = MixRecipes.Range("A1:A16")

    MixIDFilter.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=RawIngList.Range("I2"), _
    Unique:=True

[stnkynts]

Works fine for me. Do you have the right sheet names? Correct Range (Should it be A2:A16)?

[bananajelly]

Thanks stnkynts. I figured out the problem, but I don't understand why. Let me give you another example. Here is my code:

Sub Test()

Dim Macro1, Macro2 As Worksheet

Set Macro1 = Worksheets("Macro1")
Set Macro2 = Worksheets("Macro2")

Macro1.Range(Cells(1, 1), Cells(16, 6)).Sort _
    Key1:=Columns(4), _
    Order1:=xlAscending

End Sub

I notice that when I am active in the Macro1 worksheet, it works fine. But when I move to the Macro2 worksheet and run the same code, I get a Run-time error 1004. I don't understand why I cannot work across several worksheets when I defined both worksheets.

The same thing happened with the AdvancedFilter application I posted earlier. There, I set a command button in Sheet 2. When I run the code while having Sheet 1 active, it runs fine. But when I click on the command button in Sheet 2 or when I am active in Sheet 2, I get the Run-time error 1004 again.

Also, I have the codes written inside a module of the workbook, not in any specific worksheet object.

[Richard Buttrey]

Thanks stnkynts. I figured out the problem, but I don't understand why. Let me give you another example. Here is my code:

[Code]

Sub Test()

Dim Macro1, Macro2 As Worksheet

Just a small general point and not in any way related to your current request, but note that although you can put several variables on the same row, you still have to define the variable type for each if you want to avoid the variable being treated as a Variant type. i.e.

Dim Macro1 As Worksheet, Dim Macro2 As Worksheet

..and as an aid to reading code subsequently it's generally regarded as good practice to have some sort of reference to the variable type in the name.
e.g stMyVariable As String, rMyVariable As Range, wsMyVariable as Worksheet, lMyVariable as Long, etc..

[bananajelly]

Richard, I never knew that! Thanks so much for telling me! I actually posted a separate question just to confirm this, and you're totally right. All this time...

[stnkynts]

I notice that when I am active in the Macro1 worksheet, it works fine. But when I move to the Macro2 worksheet and run the same code, I get a Run-time error 1004. I don't understand why I cannot work across several worksheets when I defined both worksheets.

It is because you have to reference the worksheet on each range including a cells range.

Macro1.Range(Macro1.Cells(1, 1), Macro1.Cells(16, 6)).Sort _
    Key1:=Columns(4), _
    Order1:=xlAscending

[bananajelly]

Thanks stynkynts. It worked. I had to do the same thing with Key1:=Columns(4).

Macro1.Range(Macro1.Cells(1, 1), Macro1.Cells(16, 6)).Sort _
    Key1:=Macro1.Columns(4), _
    Order1:=xlAscending

[bananajelly]

I'm having the same problem again! I get a run-time error 1004. Am I declaring my variables incorrectly? I don't know why it works sometimes, but not other times.

Here is my code:

Sub filtering()


    Dim magic As Worksheet
    Dim IngList As Range

    Set magic = Worksheets("magic")
    Set IngList = magic.Range("A2:A13")
    
    IngList.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=magic.Range("D2"), _
    Unique:=True
    

End Sub