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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try adding "Option Explicit" to the top of your code.
 
Upvote 0
Probably you need to post the whole code for helpers to see what could possibly the cause
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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. :(
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top