Advanced filter from closed workbook

[rosanel]

Hi,


I am trying to pull data from a closed workbook using advanced filter.


here is what my code looks like right now. It works fine if I have both the workbook with the criteria as well as the workbook "ORDERS CHART" open

    Sub Macro1()
        Range("Sheet2!A1:AU999").Clear
    Range("'C:\Users\yrosanel\Downloads\[ORDERS CHART.xls]Data'!A1:AU999").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "J1:K2"), CopyToRange:=Range("Sheet2!A1:AU1"), Unique:=False
    End Sub

However, if "ORDERS CHART" is closed, I get the following error:

Run-time error '1004':
Method 'Range' of object'_Global' failed

Any help would be greatly appreciated! I have been stuck for days!