VBA Error (but only on one sheet): Object variable or With block variable not set

[anolinde]

I'm using Excel in Microsoft Office 365 on Windows 10. I've been using a macro to sort two tabs on my spreadsheet, which are identical in format, and after months of working perfectly, now the macro is not working in Tab A of the spreadsheet even though it works fine in Tab B. Whenever I try to run it, I receive the following error: "Run-time error '91': Object variable or With block variable not set"

I tried the "Debug" feature and it highlighted the first line of my code:

ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear

Oddly, as I was trying to fix this, even when I deleted this section of the macro, it just kept highlighting the first line of my code, no matter what that first line was. I'm a total newbie when it comes to debugging, though, so maybe there's something else I need to do to find the correct string of code that's causing the issue?

In any event, I have no idea what the problem is because this macro is working perfectly in Tab B. I have not made any changes to Tab A apart from the usual data entry - the format is still the same, no added or removed columns, etc.

I've pasted the full code below.

Sub WritingProjectSort()
'
' WritingProjectSort Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveSheet.AutoFilter.Sort.SortFields.Add2 Key _
        :=Range("AA1:AA252"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveSheet.AutoFilter.Sort.SortFields.Add2 Key _
        :=Range("R1:R252"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveSheet.AutoFilter.Sort.SortFields.Add2 Key _
        :=Range("N1:N252"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveSheet.AutoFilter.Sort.SortFields.Add2 Key _
        :=Range("F1:F252"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range(Selection, Selection.End(xlUp)).Select
End Sub

(If it helps to know what I'm trying to do here, I have a list of writing projects that are undergoing various types of review and have all been sent out on different dates. I am trying to organize primarily by review type (Column F), then the date the project was sent for review (Columns N, R, and AA depending on the review type), and finally alphabetical order by writing project name (Column A) so that any project not undergoing review will be listed alphabetically after all the others undergoing review.)

Thank you very much for any help you can give me.

[#2 · Accepted solution]

@anolinde 

You'd get this error if the range doesn't have filter turned on, so that ActiveSheet.AutoFilter is undefined. Add the following lines at the beginning of the macro:

    If Not ActiveSheet.AutoFilterMode Then
        Range("A1").AutoFilter
    End If

[#3]

@Hans Vogelaar Thank you!! This completely solved the problem. When you say "range doesn't have the filter turned on," what do you mean by that? I will keep this in mind going forward so I don't make the same mistake again.

[#4]

@anolinde 

On a sheet where the original code worked, you'll see filter arrows in the cells in row 1:

If you don't see those arrows, Filter hasn't been turned on and the code will fail. The lines that I suggested will turn on the filter arrows (it's the equivalent of selecting Sort & Filter > Filter on the Home tab of the ribbon)

 

[#5]

@Hans Vogelaar Thank you for explaining! That's interesting, I actually do have the filters turned on in both tabs of the spreadsheet. So I'm not sure why it wasn't working... but either way, your solution fixed it! Thanks again, I appreciate it.

Edit - or maybe your code fixed it *facepalm* Anyway, I'll pay closer attention to this going forward!