All my macros are receiving Automation error when performing sort functions

clovely

New Member
Joined
Jun 9, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Today when I when I run macros that worked fine yesterday(and several months before) they throw this error when they hit a sort operation:

1623272174484.png


Here is a sample of the code that is errors. The orange code is highlighted yellow in the VBA editor when I debug:

vEnd = Range("B65536").End(xlUp).Row
ActiveWorkbook.Worksheets("Backlog").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Backlog").Sort.SortFields.Add Key:=Range( _
"C4:C" & vEnd), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers


Any previously fine macro that has a sort operation is throwing this error. Also if I record a new macro and sort a file then try to rerun that macro I will get the same error. I tried doing a repair on my Office and still get the error. I am currently stuck and not sure what to try to fix this.

This is on a Windows 10 PC running Office 365.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,479
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Try adding "Option Explicit" to the top of your code.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,770
Office Version
  1. 2016
Platform
  1. Windows
Probably you need to post the whole code for helpers to see what could possibly the cause
 

clovely

New Member
Joined
Jun 9, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
This can't be a code issue. This is affecting several macros that have worked for months...some for years. I'm assuming something has changed on my Excel or PC settings. I checked with IT and no patches or updates have been pushed recently.

I just recorded this macro:
1623327205012.png


When I run it I get the same error.
 

clovely

New Member
Joined
Jun 9, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I just noticed that I cannot do a custom sort in Excel without it crashing. This is manually without using a macro.
1623342638927.png
I'm sure this is a related problem.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,770
Office Version
  1. 2016
Platform
  1. Windows
I just noticed that I cannot do a custom sort in Excel without it crashing. This is manually without using a macro. View attachment 40550 I'm sure this is a related problem.
Most likely you have a corrupted 365. Not familiar with 365 but it looks like you need repair/reinstall. :(
 

clovely

New Member
Joined
Jun 9, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

We did try a full repair, but that didn't correct the problem. I ask IT to reinstall while I was out Friday and I still have the problem, but I don't think they did a complete reinstall as all my macros and settings are still in place. I'll follow up with them today.
 

clovely

New Member
Joined
Jun 9, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
More information: I started Excel in safe mode and was able to sort data manually. So I manually opened my personal macro work book while in safe mode and the macros work fine. Any ideas where I should be looking?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,479
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If you have add-ins installed, try removing those and see if your sort works without being in safe mode. If it does work properly, start adding your add-ins back one by one and test for sorting after each one is added back.
 

clovely

New Member
Joined
Jun 9, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
All add ins are unchecked. I noticed this appears at the bottom of the app when you try to sort:
1623763905616.png

It doesn't do anything and doesn't lock excel up.
 

Forum statistics

Threads
1,144,340
Messages
5,723,804
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top