budakhan81
11-16-2016, 07:11 AM
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....
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....