Walshie
08-06-2010, 11:14 AM
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:thumb
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
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:thumb
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