Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How sort my working sheet not the main sheet?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How sort my working sheet not the main sheet?
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 -- Regards, Tom Ogilvy "HotRod" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How sort my working sheet not the main sheet?
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 "HotRod" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How sort my working sheet not the main sheet?
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 -- steveB Remove "AYN" from email to respond "HotRod" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How sort my working sheet not the main sheet?
Thanks a bunch.
I think this original sort may actually have came from Tom, not sure though... "HotRod" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto filter sort ascending not working while sheet protected | Excel Worksheet Functions | |||
adding new data to another sheet from the main sheet | Excel Discussion (Misc queries) | |||
Summarizing data on one main sheet | Excel Worksheet Functions | |||
How do I combine data from several sheets into one main sheet? | Excel Worksheet Functions | |||
Selection from list on main sheet from suplemental sheet in same w | New Users to Excel |