Macro not working for other colleagues

[Lucas46]

Hi,
I often create "sort" macros to sort entries in a certain order.
They work fine for me wherever I login for my work, at home, in the office, on different work stations. But when other colleagues open the file and click on the button I inserted, it comes up with an error code.
They do click on the "enable macros" message that pops up.
Does anyone know what that can be?
Any help appreciated

 

[Lucas46]

...I am not doing too good am I on my first post.
I'll get back with more info.

That's 2 out of 3, but you didn't answer the 3rd question!

Usually, when you get the error message, you will get an option to click a "Debug" button.
When you click that, it should highlight the offending line of code in yellow.
It would be very helpful to see, and to see all your code.

Also, did you confirm what version of Excel the users getting this error are using?
If they are using an older version than you, you may be using code that has new arguments that their versions do not have.

 

[Lucas46]

Please see screenshot of error yellow highlighted code below.
Macro was created using the "Record Macro" tool.
Like I said, when I click on the button I inserted the macro runs fine for me, when my colleague opens the same file and clicks the button, she gets this message.

 

[Joe4]

I think that may be one of the things that changed in later versions of Excel.
So confirm what version of Excel the users who are getting the error are running.

 

[Lucas46]

I think that may be one of the things that changed in later versions of Excel.
So confirm what version of Excel the users who are getting the error are running.

Where can I find the version information, I assumed we are all on the 365 version, but I also use the desktop application.
I am just thinking, if my colleague is still using her desktop (I drag my laptop everywhere and use a docking station) she might still have an older version on there, all our laptops have Microsoft 365 installed.
No, she also uses her laptop with 365 on it...

 

[Joe4]

Have them record the same sorting macro from their computer, and compare your VBA code, and see if there is any difference.

 

[Lucas46]

Have them record the same sorting macro from their computer, and compare your VBA code, and see if there is any difference.

I'll try that, many thanks so far

 

[Georgiboy]

I have just noticed that it looks like you are running it on the active sheet, however, the code is fixed to run on a sheet named: DATES

As not all of the ranges are qualified, to run this on a sheet named: DATES when DATES is not the active sheet you could try:

Sub SORT_ss()
  Dim ws As Worksheet
  
  Set ws = Worksheets("DATES")
  
  With ws
    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 .Range("D7:D400"), xlSortOnValues, xlAscending, xlSortNormal
    .Sort.SortFields.Add2 .Range("J7:J400"), xlSortOnValues, xlAscending, xlSortNormal
    With .Sort
      .SetRange ws.Range("A6:AZ400")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  End With
End Sub

If you wanted it to work on ANY active sheet then you could change the line in the above from:

Set ws = Worksheets("DATES")

To:

Set ws = ActiveSheet

 

[Joe4]

You may want to have them try changing "Add2" in your code to "Add".
("Add2" was added in later versions of Excel).

 

[Fluff]

Another option that should work for all versions

Sub Lucas()
   With Sheets("dates")
      .Range("A6:AZ400").Sort .Range("D6"), xlAscending, .Range("J6"), , xlAscending, , , xlYes
   End With
End Sub