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