VBA Run-time error '91' : Object variable or with block variable not set

[bmthomps]

I am getting the stated error when I run the macro. I believe it is because I need to specify the range I want the sort to be applied to. I have put the debug highlighted row in bold. How do I code it so that it applies the filter and sort to B26:AN229 ?

ActiveWorkbook.Worksheets("NEW - BW").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("NEW - BW").AutoFilter.Sort.SortFields.Add Key:= _
Range("E26:E229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("NEW - BW").AutoFilter.Sort.SortFields.Add Key:= _
Range("G26:G229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("NEW - BW").AutoFilter.Sort.SortFields.Add Key:= _
Range("D26:D229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("NEW - BW").AutoFilter.Sort.SortFields.Add Key:= _
Range("AC26:AC229"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("NEW - BW").AutoFilter.Sort.SortFields.Add Key:= _
Range("B26:B229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("NEW - BW").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$B$25:$AN$229").AutoFilter Field:=6, Criteria1:= _
"=available - new", Operator:=xlOr, Criteria2:="=Available - Resale"
End Sub

 

[par60056]

Is autofilter turned on?

Try moving your autofilter line at the beginning:

You can also make the code easier by using a With statement to keep from repeating the same things.

ActiveWorkbook.Worksheets("NEW - BW").Range("$B$25:$AN$229").AutoFilter Field:=6, Criteria1:= _
"=available - new", Operator:=xlOr, Criteria2:="=Available - Resale"




With ActiveWorkbook.Worksheets("NEW - BW").AutoFilter
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("E26:E229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("G26:G229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("D26:D229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("AC26:AC229"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("B26:B229"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[bmthomps]

I tried that and now it gives me the error '9' Subscript out of range. The highlighted lines are the top 2

 

[par60056]

There could be several issues.

1) Is the sheet name correct?
2) you list 2 criteria. 1 is mixed case and the other is all lower case. Do these match values in Column G?
3) Note that since the range starts in Column B, the 6th field is G. Is that the field that you want to filter?