Problem With VBA Sort Code...

[ByerRA]

I've been beating my head agianst the wall for two hours on this...

I can not get the following little piece of code to run properly, I keep getting a "Application-defined or object-defined error" when it adds the first sort key.

I've even recorded the macro directory from Excel and still get the error, I've recorded the sort in Office 2003 and still get the same error. I defined the sort range before the sort, I've tried just about everthing I could find though Google..

No matter what I do, I can't get this to work.

Any and all help would be appreciated as it's driving me nuts!!!

Public Sub Test_Format(File_Name As String)
 
Dim xls As Excel.Application
Dim Compare_Workbook As Excel.Workbook
Dim Number_Rows As Long
 
    On Error GoTo Format_ERROR
    Set xls = GetObject(, Excel.Application)
    If xls.Application.DisplayAlerts Then
        xls.Application.DisplayAlerts = False
    End If
    Set Compare_Workbook = xls.Workbooks.Open(File_Name)
    Do Until Compare_Workbook.Sheets.Count = 4
        Compare_Workbook.Sheets(1).Copy _
            After:=Compare_Workbook.Sheets(1)
    Loop
 
    Number_Rows = CLng(Compare_Workbook.Worksheets(Sheet_Count).UsedRange.Rows.Count)
    Compare_Workbook.Worksheets(Sheet_Count).Sort.SortFields.Clear
    Compare_Workbook.Worksheets(Sheet_Count).Sort.SortFields. _
          Add Key:=Range("$L$2:$L$" & CStr(Number_Rows)), _
          SortOn:=xlSortOnValues, Order:=xlAscending, _
          DataOption:=xlSortNormal
    Compare_Workbook.Worksheets(Sheet_Count).Sort.SortFields. _
          Add Key:=Range("$Y$2:$Y$" & CStr(Number_Rows)), _
          SortOn:=xlSortOnValues, Order:=xlDescending, _
          DataOption:=xlSortNormal
    With Compare_Workbook.Worksheets(Sheet_Count).Sort
         .SetRange Range("$A$1:$AJ$" & CStr(Number_Rows))
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
    Compare_Workbook.Close SaveChanges:=True
    xls.Application.Quit
    If Not Compare_Workbook Is Nothing Then
        Set Compare_Workbook = Nothing
    End If
    If Not xls Is Nothing Then
        Set xls = Nothing
    End If
    Exit Sub
 
 
Format_ERROR:
    If Err = 429 Then
        Err.Clear
        Set xls = CreateObject("Excel.Application")
        Resume Next
    End If
    If Not Compare_Workbook Is Nothing Then
        Compare_Workbook.Close SaveChanges:=False
        Set Compare_Workbook = Nothing
    End If
    If xls.Application.DisplayAlerts Then
        xls.Application.DisplayAlerts = False
    End If
    xls.Application.Quit
    If Not xls Is Nothing Then
        Set xls = Nothing
    End If
    Exit Sub
 
End Sub

 

[MrBillBenson]

Thanks, resolved it with former post.

Using this... with no need to select anything as far as I can tell.



wTemp.Range(wTemp.Columns(1), wTemp.Columns(2)).Sort Key1:=wTemp.Cells(1, 2), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortTextAsNumbers