Excel XP error in VBA

[Walshie1987]

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 the first instance of the following:

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

<!-- BEGIN TEMPLATE: bbcode_code -->

[LEFT]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[/LEFT]

 

[VoG]

Try replacing

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

with

   With ActiveWorkbook.Worksheets(tempHome)
        .Range("A36:M1760").Sort Key1:=.Range("A2:A1768"), Order1:=xlAscending, Key2:=.Range("C2:C1768"), Order2:=xlAscending, _
        Header:=xlYes, MatchCase:=False, Orientation:=xlTopToBottom
   End With

 

[tweedle]

Backward compatability issue: MSDN Reports that the SortFields object wasn't available until 2007: http://msdn.microsoft.com/en-us/library/bb178517(v=office.12).aspx , interesting that it runs in the 2003 version.

 

[Walshie1987]

Cheers for the replies, the above solution worked!

I'll have to double check the 2003 compatibility, sent it to someone who told me they had office 2003, but they may have got it wrong.

I am now getting the same error on the below:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        tempHome & "!R1C1:R65536C4", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=tempBQMasterstock & "!R3C1", TableName:="PivotTable", DefaultVersion _
        :=xlPivotTableVersion12

Again I think it is a compatibilty problem. For this bit of the code i recorded a macro in Office 2007 and then ameded it slightly, trying to save time, but obviously this hasn't worked!

Thanks again
Chris

 

[VoG]

I think that your best bet would be to record a macro using Excel 2003 or earlier. The pivot code (like the sort code) should be forwards compatible.