And the key1 has to be on the same sheet:

Sheets(sheetnames(shnames)).cells.sort _
Key1:=Sheets(sheetnames(shnames)).Range("L2"), _
....

Joel wrote:

I didn't test my last posting I forgot the cells. Sort needs a range and a
sheet is not a range. Your need sheets("sheet1").cells which is a range.

Don forgot the cells also so his code will also give an error.

Otto: You need tried to record a sort macro. the original code posted does
work. The record macro when you select a column only puts in L1 as the sort
range, but really sorts the whole column.

Sub sort()

sheetnames = Array("UK Purchasing Data", _
"EJ200 Data Sheet", "UK Purchasing Data 1203", _
"UK Purchasing Data 1103", "UK Purchasing Data 2103", _
"UK Purchasing", "UK Purchasing Data 1303", _
"UK Purchasing Data Spares")

For shnames = 0 To (UBound(sheetnames) - 1)
Sheets(sheetnames(shnames)).cells.sort _
Key1:=Range("L2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Next shnames

"Don Guillett" wrote:

Without looking too closely or testing something like this

Put the sheet names in an array

myarray = Array("sheet6", "sheet13", "etc")
For Each sh In myarray
'MsgBox sh
sh.range("yourrange").Sort Key1:=sh.Range("L20"), Order1:=xlDescending,
Key2:= _
sh.Range("E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom
Next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Big H" wrote in message
...
Hi There, Hopefully someone can help me, I have recorded the macro below
which works fine, however it's very large. Basically all I need is for the
the following sheets.

("UK Purchasing Data"); ("EJ200 Data Sheet"); ("UK Purchasing Data 1203");
("UK Purchasing Data 1103"); ("UK Purchasing Data 2103"); ("UK Purchasing
Data 1303"); ("UK Purchasing Data Spares"). To be sorted ascending order
on range L2


any help would be appreciated.

Big H


Sub Sort_Desending_Order()
'
' Sort_Desending_Order Macro
' Macro recorded 17/08/2007 by BigH
'

'
Range("A19").Select
Selection.Sort Key1:=Range("L20"), Order1:=xlDescending, Key2:=Range( _
"E20"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom
myarray = Array("sheet6", "sheet13")
For Each sh In myarray
MsgBox sh

Next Sheets("UK Purchasing Data 1203").Select
Range("G1").Select
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("UK Purchasing Data 1103").Select
Range("H1").Select
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("UK Purchasing Data 2103").Select
Range("L1").Select
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("UK Purchasing Data 1303").Select
Range("L2").Select
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("UK Purchasing Data Spares").Select
Range("L1").Select
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending,
Key2:=Range("E2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("UK Purchasing Data").Select
Range("A1").Select
End Sub




--

Dave Peterson