advanced filter code - subscript out of range/'Name' of object '_Worksheet' failed

[budakhan81]

Hi,

Can anyone offer me some advice where I'm going wrong with this VBA code/macro I'm trying to write please? I've exhausted my limited VBA knowledge and google!

I'm trying to create a delivery summary in excel, I have the following sheets set up :

Sheet2 (Continental Tracker) which contains data for multiple projects including delivery dates, I made this data including the column headers as named range "CONDATA".

Sheet1 (Drop Downs Do Not Delete) has the criteria for certain columns - ie. delivery date, project status, I made this named range "CRIT1"

Sheet11 (Delivery & On Hold Summary) contains the column headers I want to see in the summary, I made these column headers named range "DELHEADINGS".
Sheet11 also contains a button linked to the macro.

When I click the button I want to advanced filter "CONDATA" based on "CRIT1" and copy it to "DELHEADINGS".

The code I'm using is :

' apply filter
Range("CONDATA").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("CRIT1"), CopyToRange:=Range("DELHEADINGS"), Unique:=False

This worked for about a week but then just stopped working. After I added an errorcatcher code in it said - "Method 'Range' of object '_Worksheet' failed"

I thought it was something to do with the fact I didn't specify the ranges are on different sheets, so I changed it to :

' apply filter
Worksheets("Continental Tracker").Range("CONDATA").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Worksheets("Drop Downs Do Not Delete").Range("CRIT1"), _
    CopyToRange:=Worksheets("Delivery & On Hold Summary").Range("DELHEADINGS"), Unique:=False

now it comes up with another error - "Subscript out of range" or "The extract range has a missing or illegal field name"

Can someone help me out with where I've gone wrong please, I think I may be specifying the sheets wrong maybe but I'm not sure....

[SamT]

Check the Defined Names on each Worksheet

[budakhan81]

thanks for the reply, I checked the defined names in Name Manager and they all seem ok :

Capture.JPG

Is it something to do with how I'm calling up sheets in the VBA code?

or maybe where I have the named ranges (workbook) etc?

Thanks

[mancubus]

can you post your wokbook.
see my signature to do that.