Hello gurus,
I've been using the same macro to sort a spreadsheet for years... today it decided to present me with an error for some reason.
"Run-time error '1004':
AutoFilter method of Range class failed
Here's the complete macro (created with the "Record Macro" button in Developer menu). The bold/red section is what's highlighted in the VB debugger:
Sub Auto_Sort()
'
' Auto_Sort Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=4
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=6
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=7
Range("G2").Select
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"F2:F4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"G2:G4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"D2:D4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=4, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/15/2020")
ActiveWindow.SmallScroll Down:=-6
Range("E3").Select
End Sub
It's supposed to filter to only show all rows with a date between 1/1/20 and 12/31/20.
The other sorting macro I use on this spreadsheet still works perfectly. Appreciate any wisdom you might be able to share on how I fix this. Thank you!
I've been using the same macro to sort a spreadsheet for years... today it decided to present me with an error for some reason.
"Run-time error '1004':
AutoFilter method of Range class failed
Here's the complete macro (created with the "Record Macro" button in Developer menu). The bold/red section is what's highlighted in the VB debugger:
Sub Auto_Sort()
'
' Auto_Sort Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=4
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=6
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=7
Range("G2").Select
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"F2:F4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"G2:G4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort.SortFields.Add2 Key:=Range( _
"D2:D4619"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("SoC").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$2:$N$4619").AutoFilter Field:=4, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/15/2020")
ActiveWindow.SmallScroll Down:=-6
Range("E3").Select
End Sub
It's supposed to filter to only show all rows with a date between 1/1/20 and 12/31/20.
The other sorting macro I use on this spreadsheet still works perfectly. Appreciate any wisdom you might be able to share on how I fix this. Thank you!