sort on not active sheet

[peter_bhp]

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.

 

[JE McGimpsey]

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

 

[Sharad]

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

 

[Don Guillett]

does this help?
Sub sort()
With Sheets("sheet1")
.Range("a8:s57").sort Key1:=.Range("s8"), Order1:=xlAscending,
Header:=xlGuess
End With
End Sub

 

[Peter]

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