Runtime Error 438: Running a 2007 Macro in 2003

[bobaroski2121]

I have been trying to convert a macro I am running in 2007 to 2003. I understand that the major difference is the sort function. I have been trying to update the macro with the 'Range.Sort(' to be used in 2003 but I have been unsuccessful. Where do I need to add the 'range.sort' in order for this macro to work in both '07 and '03?

Sub Macro1()'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Dim sSheetName As String
    sSheetName = ActiveSheet.Name
    
    Range("A25:O2000").Select
    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Add Key:=Range( _
        "A26:A2000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(sSheetName).Sort
        .SetRange Range("A25:O2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this in a copy of your workbook. It should work in old and new versions.

Sub Sort_Macro()
  Range("A25:O2000").Sort Key1:=Range("A26"), Order1:=xlDescending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

 

[bobaroski2121]

Hi,

Thanks for the reply. should i be replacing my current code with this new string or adding it in? I want to make sure the code is transferable when a copy of the sheet is made and renamed.

 

[shg]

It replaces your code in its entirety.

 

[bobaroski2121]

I have a seperate macro that calls on the previous 'Macro1'. I tried replacing it entirely and renaming to work with my additional macro and it didnt work on either 2007 or 2003. One of the pivotal features if for the code to work when the worksheet is renamed. How can I make the two macros work together?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A26:A2000")) Is Nothing Then


Call Macro1


End If


End Sub

Sub Macro1() '' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Dim sSheetName As String
    sSheetName = ActiveSheet.Name
    
    Range("A25:O2000").Select
    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Add Key:=Range( _
        "A26:A2000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(sSheetName).Sort
        .SetRange Range("A25:O2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[shg]

You're still using your code, not Peter's. Renaming the sheet doesn't matter, because it operates on the active worksheet.

 

[bobaroski2121]

OK, the macro is sorting the data correctly now, but it is not doing it automatically and it is not allowing it to work with merged cells. anyway to make it work with those variables?

 

[shg]

but it is not doing it automatically ...

Post the current code.

... and it is not allowing it to work with merged cells

Unmerge the cells.

 

[bobaroski2121]

Current code:

Sub Sort_Macro()
  Range("A25:O2000").Sort Key1:=Range("A26"), Order1:=xlDescending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

 

[bobaroski2121]

Hey thanks, I was able to make it work with my original macro to call to the new macro automatically. It works on both 07 and 03. Ill just add in a macro to unmerge and merge the cells back and it should work fine. this was my first post to this forum and it worked out perfectly. thanks again