Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort on not active sheet
Hello everyone,
I've tried finding my answer in this newsgroup but couldn't find it using the subject line of this message. This macro doesn't work: Sub Sort(Object As String) Debug.Print Chr(13) & "****Begin subSort****" & Chr(13) Debug.Print " Object = " & Object Sheets(Object).Range("A8:S57").Sort _ Key1:=Range("S8"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Debug.Print Chr(13) & "****End subSort****" & Chr(13) End Sub This macro is called from a Worksheet_Change event. I've adjusted the code from a recorded macro (which does work) to include the sheet name that has the range that was mentioned ( range("A8:S57") ) Wouldn't it be great to have an answer to this message this evening of all evenings? (At this moment it is 18:05 in Holland, so ...) Thank you in advance. Peter E. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort on not active sheet
First, I would never recommend using a reserved word (Object) as a
variable name. I think the problem is your Key1:=Range("S8") line. If the macro is an a worksheet module, that range will default to that worksheet's S8. If it's in a regular code module, it defaults to the ActiveSheet. This works: Public Sub SheetSort(sWSName As String) With Sheets(sWSName) .Range("A8:S57").Sort _ Key1:=.Range("S8"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With End Sub In article . com, "peter_bhp" wrote: I've tried finding my answer in this newsgroup but couldn't find it using the subject line of this message. This macro doesn't work: Sub Sort(Object As String) Debug.Print Chr(13) & "****Begin subSort****" & Chr(13) Debug.Print " Object = " & Object Sheets(Object).Range("A8:S57").Sort _ Key1:=Range("S8"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Debug.Print Chr(13) & "****End subSort****" & Chr(13) End Sub This macro is called from a Worksheet_Change event. I've adjusted the code from a recorded macro (which does work) to include the sheet name that has the range that was mentioned ( range("A8:S57") ) Wouldn't it be great to have an answer to this message this evening of all evenings? (At this moment it is 18:05 in Holland, so ...) Thank you in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort on not active sheet
Are you getting Subscript out of range error?
Then either the argument 'Object ' is not correctly passed when you call the Sub. IF you are sure you pass it correct in your code before Sheets(Object) try Object = Trim(Object) . And finally don't use Sub name 'Sort' and Variable name Object. Use something else which does not conficits with excel in-built commands, constants, properties etc. Sub mySorting(myObject as String would be better :-) Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort on not active sheet
Thank you, JE, for your 'With - End With' solution. I've applied it to my
code and it works. By the way: Its true about the risk that is taken when using such a reserved word as your own variable, but that doesn't seem to cause any problems so far. I might shorten it Obj in a later fase. Merry Christmas. Peter "JE McGimpsey" schreef in bericht ... First, I would never recommend using a reserved word (Object) as a variable name. I think the problem is your Key1:=Range("S8") line. If the macro is an a worksheet module, that range will default to that worksheet's S8. If it's in a regular code module, it defaults to the ActiveSheet. This works: Public Sub SheetSort(sWSName As String) With Sheets(sWSName) .Range("A8:S57").Sort _ Key1:=.Range("S8"), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
Copy sheet and make new sheet active | Excel Discussion (Misc queries) | |||
Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
sort ascending tab is not active. Why? | Excel Worksheet Functions |