Thanks Dave, It now works perfectly, though I have another problem with this
code. What I would like to to with this same array is delete rows 1:19 and
then sort the data (as per the perfectly working text. I tried the following
code
Sheets(sheetnames(shnames)).Range("1:19").Select
Selection.Delete Shift:=xlUp
after the code you wrote below, however it comes up with a "Select method of
range error" do you have any ideas?

regards Big H

"Dave Peterson" wrote in message
...
You could use:
For shnames = lbound(sheetnames) To (UBound(sheetnames)


Big H wrote:

Joel, sorry for being a nuisance, the code is 99.5% working, the only
problem I have is that the last sheet doesnt get sorted ("UK Purchasing
Data
Spares"). I know it's because of @For shnames = 0 To
(UBound(sheetnames) -
1)" as when I change - 1 to - 2, the sort misses the last 2 sheets in the
array. Do you have any suggestions on how to fix this.

regards Big H

Sub sort()

sheetnames = Array("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")

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

End Sub
"Joel" wrote in message
...
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