Thread
:
Tidying Recorded Macro Code
View Single Post
#
4
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
Tidying Recorded Macro Code
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett