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

%3CLINGO-SUB%20id%3D%22lingo-sub-2056618%22%20slang%3D%22en-US%22%3EVBA%20Error%20(but%20only%20on%20one%20sheet)%3A%20Object%20variable%20or%20With%20block%20variable%20not%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2056618%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20Excel%20in%20Microsoft%20Office%20365%20on%20Windows%2010.%20I've%20been%20using%20a%20macro%20to%20sort%20two%20tabs%20on%20my%20spreadsheet%2C%20which%20are%20identical%20in%20format%2C%20and%20after%20months%20of%20working%20perfectly%2C%20now%20the%20macro%20is%20not%20working%20in%20Tab%20A%20of%20the%20spreadsheet%20even%20though%20it%20works%20fine%20in%20Tab%20B.%20Whenever%20I%20try%20to%20run%20it%2C%20I%20receive%20the%20following%20error%3A%20%22%3CSPAN%3ERun-time%20error%20'91'%3A%20Object%20variable%20or%20With%20block%20variable%20not%20set%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20tried%20the%20%22Debug%22%20feature%20and%20it%20highlighted%20the%20first%20line%20of%20my%20code%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EOddly%2C%20as%20I%20was%20trying%20to%20fix%20this%2C%20even%20when%20I%20deleted%20this%20section%20of%20the%20macro%2C%20it%20just%20kept%20highlighting%20the%20first%20line%20of%20my%20code%2C%20no%20matter%20what%20that%20first%20line%20was.%20I'm%20a%20total%20newbie%20when%20it%20comes%20to%20debugging%2C%20though%2C%20so%20maybe%20there's%20something%20else%20I%20need%20to%20do%20to%20find%20the%20correct%20string%20of%20code%20that's%20causing%20the%20issue%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20any%20event%2C%20I%20have%20no%20idea%20what%20the%20problem%20is%20because%20this%20macro%20is%20working%20perfectly%20in%20Tab%20B.%20I%20have%20not%20made%20any%20changes%20to%20Tab%20A%20apart%20from%20the%20usual%20data%20entry%20-%20the%20format%20is%20still%20the%20same%2C%20no%20added%20or%20removed%20columns%2C%20etc.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI've%20pasted%20the%20full%20code%20below.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20WritingProjectSort()%0A'%0A'%20WritingProjectSort%20Macro%0A'%0A'%20Keyboard%20Shortcut%3A%20Ctrl%2BShift%2BM%0A'%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Clear%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Add2%20Key%20_%0A%20%20%20%20%20%20%20%20%3A%3DRange(%22A1%3AA252%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20_%0A%20%20%20%20%20%20%20%20DataOption%3A%3DxlSortNormal%0A%20%20%20%20With%20ActiveSheet.AutoFilter.Sort%0A%20%20%20%20%20%20%20%20.Header%20%3D%20xlYes%0A%20%20%20%20%20%20%20%20.MatchCase%20%3D%20False%0A%20%20%20%20%20%20%20%20.Orientation%20%3D%20xlTopToBottom%0A%20%20%20%20%20%20%20%20.SortMethod%20%3D%20xlPinYin%0A%20%20%20%20%20%20%20%20.Apply%0A%20%20%20%20End%20With%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Clear%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Add2%20Key%20_%0A%20%20%20%20%20%20%20%20%3A%3DRange(%22AA1%3AAA252%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20_%0A%20%20%20%20%20%20%20%20DataOption%3A%3DxlSortNormal%0A%20%20%20%20With%20ActiveSheet.AutoFilter.Sort%0A%20%20%20%20%20%20%20%20.Header%20%3D%20xlYes%0A%20%20%20%20%20%20%20%20.MatchCase%20%3D%20False%0A%20%20%20%20%20%20%20%20.Orientation%20%3D%20xlTopToBottom%0A%20%20%20%20%20%20%20%20.SortMethod%20%3D%20xlPinYin%0A%20%20%20%20%20%20%20%20.Apply%0A%20%20%20%20End%20With%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Clear%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Add2%20Key%20_%0A%20%20%20%20%20%20%20%20%3A%3DRange(%22R1%3AR252%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20_%0A%20%20%20%20%20%20%20%20DataOption%3A%3DxlSortNormal%0A%20%20%20%20With%20ActiveSheet.AutoFilter.Sort%0A%20%20%20%20%20%20%20%20.Header%20%3D%20xlYes%0A%20%20%20%20%20%20%20%20.MatchCase%20%3D%20False%0A%20%20%20%20%20%20%20%20.Orientation%20%3D%20xlTopToBottom%0A%20%20%20%20%20%20%20%20.SortMethod%20%3D%20xlPinYin%0A%20%20%20%20%20%20%20%20.Apply%0A%20%20%20%20End%20With%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Clear%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Add2%20Key%20_%0A%20%20%20%20%20%20%20%20%3A%3DRange(%22N1%3AN252%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20_%0A%20%20%20%20%20%20%20%20DataOption%3A%3DxlSortNormal%0A%20%20%20%20With%20ActiveSheet.AutoFilter.Sort%0A%20%20%20%20%20%20%20%20.Header%20%3D%20xlYes%0A%20%20%20%20%20%20%20%20.MatchCase%20%3D%20False%0A%20%20%20%20%20%20%20%20.Orientation%20%3D%20xlTopToBottom%0A%20%20%20%20%20%20%20%20.SortMethod%20%3D%20xlPinYin%0A%20%20%20%20%20%20%20%20.Apply%0A%20%20%20%20End%20With%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Clear%0A%20%20%20%20ActiveSheet.AutoFilter.Sort.SortFields.Add2%20Key%20_%0A%20%20%20%20%20%20%20%20%3A%3DRange(%22F1%3AF252%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20_%0A%20%20%20%20%20%20%20%20DataOption%3A%3DxlSortNormal%0A%20%20%20%20With%20ActiveSheet.AutoFilter.Sort%0A%20%20%20%20%20%20%20%20.Header%20%3D%20xlYes%0A%20%20%20%20%20%20%20%20.MatchCase%20%3D%20False%0A%20%20%20%20%20%20%20%20.Orientation%20%3D%20xlTopToBottom%0A%20%20%20%20%20%20%20%20.SortMethod%20%3D%20xlPinYin%0A%20%20%20%20%20%20%20%20.Apply%0A%20%20%20%20End%20With%0A%20%20%20%20Range(Selection%2C%20Selection.End(xlUp)).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(If%20it%20helps%20to%20know%20what%20I'm%20trying%20to%20do%20here%2C%20I%20have%20a%20list%20of%20writing%20projects%20that%20are%20undergoing%20various%20types%20of%20review%20and%20have%20all%20been%20sent%20out%20on%20different%20dates.%20I%20am%20trying%20to%20organize%20primarily%20by%20review%20type%20(Column%20F)%2C%20then%20the%20date%20the%20project%20was%20sent%20for%20review%20(Columns%20N%2C%20R%2C%20and%20AA%20depending%20on%20the%20review%20type)%2C%20and%20finally%20alphabetical%20order%20by%20writing%20project%20name%20(Column%20A)%20so%20that%20any%20project%20not%20undergoing%20review%20will%20be%20listed%20alphabetically%20after%20all%20the%20others%20undergoing%20review.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20very%20much%20for%20any%20help%20you%20can%20give%20me.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2056618%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2058144%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Error%20(but%20only%20on%20one%20sheet)%3A%20Object%20variable%20or%20With%20block%20variable%20not%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F927576%22%20target%3D%22_blank%22%3E%40anolinde%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%20asked%20in%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fvba-error-but-only-on-one-sheet-object-variable-or-with-block%2Fm-p%2F2057506%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EVBA%20Error%20(but%20only%20on%20one%20sheet)%3A%20Object%20variable%20or%20With%20block%20variable%20not%20set%20%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

1 Reply