How sort my working sheet not the main sheet?

[HotRod]

I'm using the function below to sort my worksheet, the problem I'm having is
that everything worked fine until I copied the data to a new "Worksheet" and
want the sort to be done on the new sheet not on the "Mainsheet" even when
the worksheet has the focus the mainsheet is the one being sorted. What do I
need to do?



Function My_Column_Sort(First_Data_Row As Integer, AscendingOrder As
Boolean, _
First_Column As String, Optional Second_Column As
String = "Z", _
Optional Third_Column As String = "Z")

'Allows you to sort the columns in what ever fashion specified. _
using up to Four Columns. Need to build statement in case less then _
four Sort Columns are given.


Range("A" & First_Data_Row & ":AA55550").Sort
Key1:=Range(First_Column & First_Data_Row), _
Order1:=xlAscending, Key2:=Range(Second_Column & First_Data_Row), _
Order2:=xlAscending, Key3:=Range(Third_Column & First_Data_Row), _
Order3:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
'AA55550
End Function

 

[Tom Ogilvy]

If this is in a general module, it will work on the active sheet. If you
have it in a sheet module, then it will sort on the sheet that contains the
code. If you want to specify what sheet, add the sheet as an argument



Function My_Column_Sort(shName as string, _
First_Data_Row As Integer, _
First_Column As String, _
Optional Second_Column As String = "Z", _
Optional Third_Column As String = "Z")

'Allows you to sort the columns in what ever fashion specified. _
'using up to Three Columns. Need to build statement in case less then _
'Three Sort Columns are given

Dim sh as Worksheet
set sh = Worksheets(shName)
sh.Range("A" & First_Data_Row & ":AA55550").Sort _
Key1:=sh.Range(First_Column & First_Data_Row), _
Order1:=xlAscending, Key2:=sh.Range(Second_Column & First_Data_Row),
_
Order2:=xlAscending, Key3:=sh.Range(Third_Column & First_Data_Row), _
Order3:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
'AA55550
End Function

 

[Guest]

Hi,
Something like (assuming you calling the function from VBA):

Dim ws as worksheet

Set ws=Worksheets("MyWorkSheet") ' whatever worksheet you are sorting ...

My_Column_Sort(ws, ..... ' Add ws as parameter to your function

and the qualify range :

ws.Range("A" & First_Data_Row & ":AA55550").Sort

HTH

 

[STEVE BELL]

You just need to designate the worksheet and the range
Worked in Excel 2000

With Sheets("Sheet2").Range("A1:A15")
.Sort Key1:=Sheets("Sheet2").Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

 

[HotRod]

Thanks a bunch.

I think this original sort may actually have came from Tom, not sure
though...