Solved: Sort without selecting the Sheet

[JimS]

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

[vba]
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
[/vba]

[p45cal]

This should give the same result:[vba]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 Sub[/vba]call with the likes of:[vba]Sub test()
SortSheets Sheets("Sheet1")
End Sub[/vba]but 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]

What if I wanted this to run on any sheet that's name begins with the name "Sheet"?

[mumin_abdul]

If Left(Sht.Name, 5) = "Sheet" Then
'Your code goes here
End if

[mdmackillop]

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]

If Left(Sht.Name, 5) = "Sheet" Then
'Your code goes here
End if

which becomes[vba]Sub test()
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 5) = "Sheet" Then
SortSheets sht
End If
Next sht
End Sub
[/vba]

[Zack Barresse]

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...

[vba]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[/vba]

[p45cal]

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]

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]

Thanks this works perfect...