Excel XP error in VBA

Walshie1987

New Member
Joined
Aug 8, 2010
Messages
10
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 -->
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]
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try replacing

Code:
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

Code:
   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
 

Walshie1987

New Member
Joined
Aug 8, 2010
Messages
10
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:

Code:
    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

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,488
Messages
5,601,974
Members
414,487
Latest member
inxlsplot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top