Excel VBA Error Code 438 Object doesn support this property of method Issue!!

Evening

Can someone help me and shed some light for me

I have a spreadsheet quite simple contains list of daa and has VBA and autofilter in order to filter criteria without need to go into filter properties yourself.  Problem is I am running Excel 2007, the original spreadsheet and code was created in 2003 and all amendments since have been done by me in 2007.  I copied the VB into exisitng worksheet today and was found to be full of errors.  Code 438 Object does not support property or method.  The code and spreadsheet when used in 2007 version works fine but not in 2003.  Any ideas

I have attached test file and also code.  

Private Sub OptionButton1_Click()
'
' ConsReport Asc Macro
'
    Dim myLastCell As Range
    Dim mySortRange As Range
    
    Set myLastCell = LastCell(ActiveSheet)
    Set mySortRange = Range("J4", Cells(myLastCell.Row - 2, 10))
    
        
    
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    
    Range("J4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub

Private Sub OptionButton2_Click()
'
' ft2Report Asc Macro
'

    Dim myLastCell As Range
    Dim mySortRange As Range
    
    Set myLastCell = LastCell(ActiveSheet)
    Set mySortRange = Range("I4", Cells(myLastCell.Row - 2, 9))
    
    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Range("I4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub

Private Sub OptionButton3_Click()
'
' ConsReport Desc Macro

   Dim myLastCell As Range
   Dim mySortRange As Range
    
   Set myLastCell = LastCell(ActiveSheet)
   Set mySortRange = Range("J4", Cells(myLastCell.Row - 2, 10))
   
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = True
    Range("J4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub

Private Sub OptionButton4_Click()
'
' ft2Report Desc Macro

   Dim myLastCell As Range
   Dim mySortRange As Range
    
   Set myLastCell = LastCell(ActiveSheet)
   Set mySortRange = Range("I4", Cells(myLastCell.Row - 2, 9))

    Columns("G").Hidden = True
    Columns("J").Hidden = True
    Columns("I").Hidden = False
    Range("I4").Select
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields. _
        Add Key:=mySortRange, SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    
End Sub
Function LastCell(ws As Worksheet) As Range
'
' Note "&" denotes a long value; "%" denotes an integer value
  
  Dim LastRow&, lastCol%

' Error-handling is here in case there is not any
' data in the worksheet

  On Error Resume Next

  With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

  End With

' Finally, initialize a Range object variable for
' the last populated row.

  Set LastCell = ws.Cells(LastRow&, lastCol%)

End Function

Sub ClearCriteria()

If ActiveSheet.AutoFilterMode = True Then

    ActiveWorkbook.Worksheets("Cons Report").AutoFilter.Sort.SortFields.Clear
    
    Columns("G").Hidden = False
    Columns("J").Hidden = False
    Columns("I").Hidden = False
    
    Else
    


End If

End Sub

Select allOpen in new window

Test-Report.xls