PDA

View Full Version : VBA Not working in Office XP



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

austenr
08-06-2010, 11:55 AM
try putting these lines in the with block

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


but only this part of the lines:

.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

Walshie
08-08-2010, 07:30 AM
Thanks for the reply, the error is appearing before the with block though, any ideas?

Cheers
Chris

Artik
08-08-2010, 02:31 PM
it works perfect in office 2003 & 2007 You do not speak the truth. :winking2:
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.