sortTable works in 2003 but not in 2007
 

Hi
I am a newbie and received a file use VB to create a league table. The
results are entered on a separate sheet and the league table updates
automatically when the league table worksheet is selected. Great in 2003 but
when i open the file in 2007, i get a "Compile error, invalid use of
property". Basically the code below seems to be the problem, but i have no
idea how to correct it.

Private Sub sortTable()
sort sortRange:=Table.Range("leagueTable"), _
sortKey1:=Range("A4"), _
sortKey2:=Range("Y4"), _
sortOrder2:=xlDescending, _
sortKey3:=Range("W4")
' Range("B3").Select
End Sub

Any help would be greatly appreciated.

sortTable works in 2003 but not in 2007
 

Excel 2007 had a different model for doing sorts. It allowed more than 3
keys, so has collection of sort keys. Turn on the macro recorder and set up
a multikey sort manually. Then turn of the macro recorder and see what the
code looks like. I don't have xl2007 installed, so I can't give you
specifics.

--
Regards,
Tom Ogilvy


"Marcello" wrote:

Hi
I am a newbie and received a file use VB to create a league table. The
results are entered on a separate sheet and the league table updates
automatically when the league table worksheet is selected. Great in 2003 but
when i open the file in 2007, i get a "Compile error, invalid use of
property". Basically the code below seems to be the problem, but i have no
idea how to correct it.

Private Sub sortTable()
sort sortRange:=Table.Range("leagueTable"), _
sortKey1:=Range("A4"), _
sortKey2:=Range("Y4"), _
sortOrder2:=xlDescending, _
sortKey3:=Range("W4")
' Range("B3").Select
End Sub

Any help would be greatly appreciated.

sortTable works in 2003 but not in 2007
 

Created a simple macro as suggested. This is a copy of the code that was
produced.

Sub Macro3()
'
' Macro3 Macro
'

'
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("D10:D31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("E10:E31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("F10:F31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").sort
.SetRange Range("D10:L31")
.Header = xlGuess
.MatchCase = False
.orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Had a look through the code and it is not apparent what part of it i can use?

Thanks in advance for your help.



"Tom Ogilvy" wrote:

Excel 2007 had a different model for doing sorts. It allowed more than 3
keys, so has collection of sort keys. Turn on the macro recorder and set up
a multikey sort manually. Then turn of the macro recorder and see what the
code looks like. I don't have xl2007 installed, so I can't give you
specifics.

--
Regards,
Tom Ogilvy


"Marcello" wrote:

Hi
I am a newbie and received a file use VB to create a league table. The
results are entered on a separate sheet and the league table updates
automatically when the league table worksheet is selected. Great in 2003 but
when i open the file in 2007, i get a "Compile error, invalid use of
property". Basically the code below seems to be the problem, but i have no
idea how to correct it.

Private Sub sortTable()
sort sortRange:=Table.Range("leagueTable"), _
sortKey1:=Range("A4"), _
sortKey2:=Range("Y4"), _
sortOrder2:=xlDescending, _
sortKey3:=Range("W4")
' Range("B3").Select
End Sub

Any help would be greatly appreciated.

sortTable works in 2003 but not in 2007
 

My guess would be:

Private Sub sortTable

Activesheet.sort.SortFields.Clear

Activesheet.sort.SortFields.Add
Key:=Range("A4"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

Activesheet.sort.SortFields.Add
Key:=Range("Y4"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

Activesheet.sort.SortFields.Add
Key:=Range("W4"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

With ActiveSheet.sort
.SetRange Table.Range("LeagueTable")
.Header = xlGuess
.MatchCase = False
.orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

--
Regards,
Tom Ogilvy


"Marcello" wrote:

Created a simple macro as suggested. This is a copy of the code that was
produced.

Sub Macro3()
'
' Macro3 Macro
'

'
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("D10:D31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("E10:E31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("F10:F31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").sort
.SetRange Range("D10:L31")
.Header = xlGuess
.MatchCase = False
.orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Had a look through the code and it is not apparent what part of it i can use?

Thanks in advance for your help.



"Tom Ogilvy" wrote:

Excel 2007 had a different model for doing sorts. It allowed more than 3
keys, so has collection of sort keys. Turn on the macro recorder and set up
a multikey sort manually. Then turn of the macro recorder and see what the
code looks like. I don't have xl2007 installed, so I can't give you
specifics.

--
Regards,
Tom Ogilvy


"Marcello" wrote:

Hi
I am a newbie and received a file use VB to create a league table. The
results are entered on a separate sheet and the league table updates
automatically when the league table worksheet is selected. Great in 2003 but
when i open the file in 2007, i get a "Compile error, invalid use of
property". Basically the code below seems to be the problem, but i have no
idea how to correct it.

Private Sub sortTable()
sort sortRange:=Table.Range("leagueTable"), _
sortKey1:=Range("A4"), _
sortKey2:=Range("Y4"), _
sortOrder2:=xlDescending, _
sortKey3:=Range("W4")
' Range("B3").Select
End Sub

Any help would be greatly appreciated.

sortTable works in 2003 but not in 2007
 

Hi Tom

Had a go using the code you suggested, but it still didn't work. Have
played around with it for some time now and finally it works, a copy of the
code is below:

Private Sub sortTable()

ActiveWorkbook.Worksheets("League Table").sort.SortFields.Clear
ActiveWorkbook.Worksheets("League Table").sort.SortFields.Add
Key:=Range( _
"A3:A24"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("League Table").sort.SortFields.Add
Key:=Range( _
"Y3:Y24"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("League Table").sort.SortFields.Add
Key:=Range( _
"W3:W24"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("League Table").sort
.SetRange Range("A2:Z24")
.Header = xlYes
.MatchCase = False
.orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Thanks for you help, without it I would never have been on the right path.

Cheers

"Tom Ogilvy" wrote:

My guess would be:

Private Sub sortTable

Activesheet.sort.SortFields.Clear

Activesheet.sort.SortFields.Add
Key:=Range("A4"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

Activesheet.sort.SortFields.Add
Key:=Range("Y4"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

Activesheet.sort.SortFields.Add
Key:=Range("W4"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

With ActiveSheet.sort
.SetRange Table.Range("LeagueTable")
.Header = xlGuess
.MatchCase = False
.orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

--
Regards,
Tom Ogilvy


"Marcello" wrote:

Created a simple macro as suggested. This is a copy of the code that was
produced.

Sub Macro3()
'
' Macro3 Macro
'

'
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("D10:D31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("E10:E31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet3").sort.SortField s.Add
Key:=Range("F10:F31") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").sort
.SetRange Range("D10:L31")
.Header = xlGuess
.MatchCase = False
.orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Had a look through the code and it is not apparent what part of it i can use?

Thanks in advance for your help.



"Tom Ogilvy" wrote:

Excel 2007 had a different model for doing sorts. It allowed more than 3
keys, so has collection of sort keys. Turn on the macro recorder and set up
a multikey sort manually. Then turn of the macro recorder and see what the
code looks like. I don't have xl2007 installed, so I can't give you
specifics.

--
Regards,
Tom Ogilvy


"Marcello" wrote:

Hi
I am a newbie and received a file use VB to create a league table. The
results are entered on a separate sheet and the league table updates
automatically when the league table worksheet is selected. Great in 2003 but
when i open the file in 2007, i get a "Compile error, invalid use of
property". Basically the code below seems to be the problem, but i have no
idea how to correct it.

Private Sub sortTable()
sort sortRange:=Table.Range("leagueTable"), _
sortKey1:=Range("A4"), _
sortKey2:=Range("Y4"), _
sortOrder2:=xlDescending, _
sortKey3:=Range("W4")
' Range("B3").Select
End Sub

Any help would be greatly appreciated.