Forum Discussion
vba coding errors
- Sep 17, 2018
The code fails because the range addresses are wrong. Remove all dollar signs at the end of the addresses, example:
change:
Sheets("Department").Range("$A$1:$AJ$1500$").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAndto:
Sheets("Department").Range("$A$1:$AJ$1500").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd
Sheets("Department").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd
Run-time error '448': Named argument not found
Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd
Run-time error '1004': Application-defined or object-defined error
full macro at this point is
Sub mcrOpen()
'
' mcrOpen Macro
' open file
'
Range("A5").Select
Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd
Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=35, Criteria1:= _
"<>*dispute*", Operator:=xlAnd
Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=11, Criteria1:="-3"
Sheets("Department").Range("$A$5:$AJ1500$").SpecialCells _
(xlCellTypeVisible).EntireRow.Copy
Workbooks.Open Filename:="C:\Users\1402324\Desktop\Payment due within 3 days.xlsx"
Range("A3").Select
ActiveSheet.Paste
It fails at first filter choice. my ultimate aim is to filter data, paste to a different workbook, open and draft email. i will do a little manual edit and add file to email, send and then run a macro for a different filter value
The code fails because the range addresses are wrong. Remove all dollar signs at the end of the addresses, example:
change:
Sheets("Department").Range("$A$1:$AJ$1500$").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd
to:
Sheets("Department").Range("$A$1:$AJ$1500").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd
- Mark LeeSep 18, 2018Copper Contributor
Thanks. I can't believe i was so blind as to miss that.