VBA Not working in Office XP

[Walshie]

Hi,

I just created some VBA for a spreadsheet, it works perfect in office 2003 & 2007 but when I open it in Office XP I get a "438 Runtime Error", "Object does not support this property or method"

When debugging the line with the error is:

ActiveWorkbook.Worksheets(tempHome).Sort.SortFields.Clear

Below is the section of code that is producing the error.

Please can someone advise me of what's going wrong.

Thanks in advance
Chris

Sheets("TrialOrderBook").Activate
    ActiveSheet.Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    tempHome = ActiveSheet.Name
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$1768").AutoFilter Field:=6, Criteria1:=">0", _
        Operator:=xlAnd
    ActiveSheet.Range("$A$1:$M$1768").AutoFilter Field:=13, Criteria1:=">0", _
        Operator:=xlAnd
    ActiveSheet.Columns("I:L").Select
    Selection.Delete Shift:=xlToLeft
    ActiveSheet.Columns("A:E").Select
    ActiveSheet.Range("E1").Activate
    Selection.Delete Shift:=xlToLeft
    ActiveSheet.Range("A1").Select
    ActiveCell.FormulaR1C1 = "ProdCode"
    ActiveSheet.Range("B1").Select
    ActiveCell.FormulaR1C1 = "Description"
    ActiveSheet.Range("C1").Select
    ActiveCell.FormulaR1C1 = "WkNumber"
    ActiveSheet.Range("D1").Select
    ActiveCell.FormulaR1C1 = "Balance"
    ActiveSheet.Cells.Select
    ActiveWorkbook.Worksheets(tempHome).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(tempHome).Sort.SortFields.Add Key:=Range("A2:A1768" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(tempHome).Sort.SortFields.Add Key:=Range("C2:C1768" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(tempHome).Sort
        .SetRange Range("A36:M1760")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

[austenr]

try putting these lines in the with block

[vba]ActiveWorkbook.Worksheets(tempHome).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(tempHome).Sort.SortFields.Add Key:=Range("A2:A1768" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(tempHome).Sort.SortFields.Add Key:=Range("C2:C1768" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
[/vba]

but only this part of the lines:

[vba].SortFields.Clear
.SortFields.Add Key:=Range("A2:A1768" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("C2:C1768" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

[/vba]

[Walshie]

Thanks for the reply, the error is appearing before the with block though, any ideas?

Cheers
Chris

[Artik]

it works perfect in office 2003 & 2007

You do not speak the truth.
This code will not work in XL 2K3. SortFields property added in version 2K7.

Record the same macro by using Excel 2K2 or 2K3. It should work well in the 2K7 version.

Artik

P.S.
I apologize for my language. I use a translator.