Joel,

I have tried running your code, however it comes up with a runtime error 438
and highlights the following code yellow

Sheets(sheetnames(shnames)).sort _
Key1:=Range("L2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Any suggestions Big H
"Joel" wrote in message
...
Here is one method putting sheet names in an array and using a for loop

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)).sort _
Key1:=Range("L2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Next shnames

End Sub


"Big H" wrote:

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
Sheets("EJ200 Data Sheet").Select
Range("E1").Select
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
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