I think you dropped a "with" keyword.

Option Explicit
Sub Sort_New_List()
with worksheets("sheet9999") '<-- change this
'or with Activesheet '?????
with .Range("B53:P133")
'column L is the 11th column in B:P
'column M is the 12th column in B:P
'column N is the 13th column in B:P
.sort key1:=.columns(11), order1:=xldescending, _
Key2:=.columns(12), Order2:=xlDescending, _
Key3:=.columns(13), Order3:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
end with
end with
End Sub

(Uncompiled and untested--watch for typos.)

And I bet you know if there are headers or not. If you do, don't let excel
guess. Supply the information yourself and you won't have to worry about excel
guessing wrong.


Ace70 wrote:

Hi, thanks for the general rule tip.
All my macros deal with stuff on the one worksheet. They do things
such as refresh pivot tables, sort lists and copy cells from one spot
to another.
I have tried going into the VBA code for the macros I have that jump
around and have tried to apply the same rules as you did to your
example. However I did get errors I don't kno whow to correct.

eg. I initially started off with:

Sub Sort_New_List()

Range("B53:P133").Select
Selection.Sort Key1:=Range("L54"), Order1:=xlDescending,
Key2:=Range( _
"M54"), Order2:=xlDescending, Key3:=Range("N54"),
Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Range("B53").Select
' I originally selected B53 as where I want the window view to be
viewing when the macro is complete

End Sub

From this, I tried deleting the "Select" cases like in the example
provided and I ended up with:

Sub Sort_New_List()

Range ("B53:P133")

Sort Key1:=Range("L54"), Order1:=xlDescending, Key2:=Range( _
"M54"), Order2:=xlDescending, Key3:=Range("N54"),
Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

End Sub

However I get an complie error invalid use of proptery where its says
Range ("B53:P133")

Any clues of tweaking this.

As a general rule you should try to avoid changing the selection in a
macro: it's almost always unnecessary, can make the macro run slower,
and (as you mention above) forgets your initial state. So for
example
instead of

Workbooks("NewBook.xls").Select
Worksheets("SomeSheet").Select
Range("A1:C20").Select
Selection.Font.Bold = True

(as typically generated when you record a macro) you can use :

Workbooks("NewBook.xls").
Worksheets("SomeSheet").Range("A1:C20").Font.Bold = True

You can do this more elegantly using varaiables to keep track of what
you're working on:

Dim wb as Workbook, ws as Worksheet, rangeToBolden as Range
Set wb = Workbooks ("NewBook.xls")
Set ws = wb.Worksheets("SomeSheet")
Set rangeToBolden = ws.Range ("A1:C20")
rangeToBolden.Font.Bold = True
' could do other stuff with ws here..

If you absolutely _have_ to move away from you initial selection,
then you can remember it at the start of the macro and
restore it at the end:

dim wbInitial as Workbook , wsInitial as Worksheet, rngInitial as
Range

' save initial state
Set wbInitial = activeworkbook
Set wsInitial = activesheet
Set rngInitial = selection
'
'' macro code here
'
'restore initial state
wbInitial.Activate
wsInitial.Select
rngInitial.Select- Hide quoted text -

- Show quoted text -

--

Dave Peterson