Tidying Recorded Macro Code

B

Big H

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
 
G

Guest

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
 
O

Otto Moehrbach

Big H
I don't understand what the code is doing. It selects one cell, then
sorts the selection. Obviously you can't sort a range of one cell.
I take it that you want to sort something in a number of sheets. Do you
want to sort the same range in every sheet? What range? Do those ranges
include headers?
I would use a For loop through an array of all the sheets you want to
sort, then sort each range. If all the ranges are the same then that's all
you would need. If not, I would use a Select Case construct to define the
range for each sheet. Post back if you need more. If you post back,
include more detail about what the ranges are or, if they are variable,
include an explanation of how the range-to-sort is determined for each
sheet. HTH Otto
 
D

Don Guillett

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
(e-mail address removed)
Big H said:
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")
Click to expand...
For Each sh In myarray
MsgBox sh

Next> Sheets("UK Purchasing Data 1203").Select
 
G

Guest

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
 
D

Dave Peterson

And the key1 has to be on the same sheet:

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

Big H

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
 
B

Big H

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
 
B

Big H

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
 
D

Dave Peterson

Don't select it. Just delete it.

Sheets(sheetnames(shnames)).Range("1:19").Delete Shift:=xlUp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple Do... Until problem 4
"Runtime error 1004 Application-defined or object defined error" on data sort 0
Simplfying the Sort macro 2
sort macro not working 1
Refernce sheet names in if statement 4
Onclick 2 events 2
Simple MultiColumn Sorting 8
SpecialCells(xlCellTypeBlanks) = error 1004 No cells found 5

Top