Method 'Sort' of object '_Worksheet' failed

[beasley101]

I have a really annoying problem whereby I can run a macro on one computer, but when running on a different computer I get the above error message.

Both computers have the same version of excel!

Below is the section of code that fails.

Dim WorkDay As String
            Dim LastRow As Integer
            
            WorkDay = ActiveSheet.Name



Dim SortData, PostCodeSort, AddressSort, NameSort, TimeSort, RouteSort, ProductSort As Range
                
            Range("A1").Select
            Selection.CurrentRegion.Select
            
           
            Set SortData = Selection
            
            Range("L2").Select
            Range(Selection, "L" & LastRow).Select
            Set PostCodeSort = Selection
            
            Range("N2").Select
            Range(Selection, "N" & LastRow).Select
            Set AddressSort = Selection
            
            Range("B2").Select
            Range(Selection, "B" & LastRow).Select
            Set RouteSort = Selection
            
            Range("I2").Select
            Range(Selection, "I" & LastRow).Select
            Set TimeSort = Selection
            
            Range("E2").Select
            Range(Selection, "E" & LastRow).Select
            Set ProductSort = Selection
            
            ActiveWorkbook.Worksheets(WorkDay).Sort.SortFields.Clear

it fails on the last line of that code. I have missed out some detail such as the LastRow code as I didn't feel it was relevant.

Please help!

Thanks

[6StringJazzer]

I cannot reproduce this error. When something runs on one computer and not another, there is usually some difference that the user isn't considering, often in the file and having nothing to do with the computers.

What does the data look like on the active sheet? How is this code being invoked? Are you sure that the intended sheet is active when this code is invoked? One suggestion would be to add a line before the line that fails to confirm the correct sheet:

MsgBox "Active sheet is [" & ActiveSheet.Parent.Name & "]" & ActiveSheet.Name

Also consider attaching your file, so we can do a better job of troubleshooting. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

I have a couple of other observations. These are not causing your problem but you might find them helpful.

Dim SortData, PostCodeSort, AddressSort, NameSort, TimeSort, RouteSort, ProductSort As Range

This declaration does not do what you think. In VB, you must specify a data type for each variable, or it will default to Variant. In this declaration, ProductSort is a Range and all the other are Variants. This doesn't cause an error but it would be a better practice to specify the data type intended.

Dim SortData As Range, PostCodeSort As Range, AddressSort As Range, NameSort As Range, TimeSort As Range, RouteSort As Range, ProductSort As Range

It is not necessary to Select things to manipulate them. When you Select, it affects the worksheet display, could disorient the user, and takes a lot longer to execute. For example, this

            Range("L2").Select
            Range(Selection, "L" & LastRow).Select
            Set PostCodeSort = Selection

could be done like this:

            Set PostCodeSort = Range("L2:L" & LastRow)

You are using an extra level of indirection to refer to the active sheet. Again, not an error, but might be a little cleaner to do this:

Dim WorkDay As Worksheet
            Set WorkDay = ActiveSheet
            
            WorkDay.Sort.SortFields.Clear