Can I sort without activating / selecting sheet?
 

I'm working at the moment on speeding up my code (already in use for
18 months) and therefore want to limit focus changes between
workbooks, sheets, etc.

Elsewhere in this newsgroup I've explained why TurningOff ScreenUpdate
isn't an option for me; in short the resulting screen after turning it
back on goes all haywire.

Using With ... End With, really does speed up things considerably. So
far however I've not managed to do a Sort without first activating the
sheet to sort.

As such the following code only works when Worksheet 'def' in workbook
'abc' is activated. Activating another workbook, or for that matter
another sheet in this workbook will lead to error 1004.


Sub Macro1()
'
With Workbooks("Abc.xls").Worksheets("def").Range("ghi" )
.Sort Key1:=Range("A2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
End Sub

The same happens when the With code is more or less implied through:

Workbooks("Abc.xls").Worksheets("def").Range("ghi" ).Sort _
Key1:=Range("A2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Any ideas as to why this won't work?

Thanks,

Leo Elbertse

Can I sort without activating / selecting sheet?
 

Try the following:

Sub Macro1()
'
With Workbooks("Abc.xls").Worksheets("def")
.Range("ghi").Sort Key1:=.Range("A2"),
Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
End Sub

The key has to be in the range to be sorted

Kevin Beckham

-----Original Message-----
I'm working at the moment on speeding up my code (already
in use for
18 months) and therefore want to limit focus changes
between
workbooks, sheets, etc.

Elsewhere in this newsgroup I've explained why TurningOff
ScreenUpdate
isn't an option for me; in short the resulting screen
after turning it
back on goes all haywire.

Using With ... End With, really does speed up things
considerably. So
far however I've not managed to do a Sort without first
activating the
sheet to sort.

As such the following code only works when
Worksheet 'def' in workbook
'abc' is activated. Activating another workbook, or for
that matter
another sheet in this workbook will lead to error 1004.


Sub Macro1()
'
With Workbooks("Abc.xls").Worksheets("def").Range
("ghi")
.Sort Key1:=Range("A2"), Order1:=xlDescending,
_
Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
End Sub

The same happens when the With code is more or less
implied through:

Workbooks("Abc.xls").Worksheets("def").Range("ghi ").Sort
_
Key1:=Range("A2"), Order1:=xlDescending, _

Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
_
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

Any ideas as to why this won't work?

Thanks,

Leo Elbertse
.

Can I sort without activating / selecting sheet?
 

Whoah,

what a rookie mistake!

Thanks Kevin,

Leo

On Sun, 28 Dec 2003 09:03:41 -0800, "Kevin Beckham"
wrote:

Try the following:

Sub Macro1()
'
With Workbooks("Abc.xls").Worksheets("def")
.Range("ghi").Sort Key1:=.Range("A2"),
Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
End Sub

The key has to be in the range to be sorted

Kevin Beckham

-----Original Message-----
I'm working at the moment on speeding up my code (already
in use for
18 months) and therefore want to limit focus changes
between
workbooks, sheets, etc.

Elsewhere in this newsgroup I've explained why TurningOff
ScreenUpdate
isn't an option for me; in short the resulting screen
after turning it
back on goes all haywire.

Using With ... End With, really does speed up things
considerably. So
far however I've not managed to do a Sort without first
activating the
sheet to sort.

As such the following code only works when
Worksheet 'def' in workbook
'abc' is activated. Activating another workbook, or for
that matter
another sheet in this workbook will lead to error 1004.


Sub Macro1()
'
With Workbooks("Abc.xls").Worksheets("def").Range
("ghi")
.Sort Key1:=Range("A2"), Order1:=xlDescending,
_
Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
End With
End Sub

The same happens when the With code is more or less
implied through:

Workbooks("Abc.xls").Worksheets("def").Range("gh i").Sort
_
Key1:=Range("A2"), Order1:=xlDescending, _

Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
_
Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

Any ideas as to why this won't work?

Thanks,

Leo Elbertse
.