PDA

View Full Version : Solved: Sort without selecting the Sheet



JimS
07-22-2009, 01:00 PM
Can this code be modified to run (run meaning be called by another macro) so that the sheet does not have to be selected?

Thanks...

Jim


Sub SortSheets()
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A4").Select
End Sub

p45cal
07-22-2009, 01:20 PM
This should give the same result:Sub SortSheets(Sht As Worksheet)
Set myRng = Sht.Range("A6")
Range(Range(myRng, myRng.End(xlToRight)), Range(myRng, _
myRng.End(xlToRight)).End(xlDown)).Sort Key1:=myRng, _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Subcall with the likes of:Sub test()
SortSheets Sheets("Sheet1")
End Subbut note that both your, and this, code sorts a block of cells the size of which is dependent on the lack of empty cells in the top row and the lack of spaces in the first column below A6.

JimS
07-24-2009, 12:13 PM
What if I wanted this to run on any sheet that's name begins with the name "Sheet"?

mumin_abdul
07-24-2009, 12:23 PM
If Left(Sht.Name, 5) = "Sheet" Then
'Your code goes here
End if

mdmackillop
07-24-2009, 12:45 PM
What if I wanted this to run on any sheet that's name begins with the name "Sheet"?

"Any" you need to specify the sheet. "All" you can loop using the suggested code.

p45cal
07-24-2009, 05:58 PM
If Left(Sht.Name, 5) = "Sheet" Then
'Your code goes here
End if
which becomesSub test()
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 5) = "Sheet" Then
SortSheets sht
End If
Next sht
End Sub

Zack Barresse
07-25-2009, 11:15 AM
The code would most likely still fail, as the sort procedure doesn't reference the worksheet on all of the range references. Change it to...

Sub SortSheets(Sht As Worksheet)
Set myRng = Sht.Range("A6")
Sht.Range(Sht.Range(myRng, myRng.End(xlToRight)), Sht.Range(myRng, _
myRng.End(xlToRight)).End(xlDown)).Sort Key1:=myRng, _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

p45cal
07-25-2009, 03:30 PM
Until recently I was never sure whether I needed to include the explicit reference to the sheet throughout as in
Sht.Range(myRng, myRng.End(xlToRight)
but I'm beginning to think you don't if there is the full ref within parentheses, which there is throughout in this case, so
Range(myRng, myRng.End(xlToRight)
seems to be just fine.
I tried it with the code I posted in this thread on multiple sheets and it seems to work.

Zack Barresse
07-26-2009, 09:50 AM
Yes, that will work. I still don't recommend it, as it's confusing, and as far as I'm concerned sloppy. Just MHO. :)

JimS
07-27-2009, 11:16 AM
Thanks this works perfect...