Return to active cell after sort
 

Is it possible to edit the Sort Routine so that when the sort is completed,
the Range("B6:G1000") will be deselected and the active cell will be the
cell that was active when the Sort Routine started?



Sub SortNames()
'
' Macro1 Macro
'

'
Application.ScreenUpdating = False

ActiveWindow.SmallScroll Down:=-6
Range("B6:G1000").Select
ActiveWorkbook.Worksheets("Jan 08").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Jan 08").Sort.SortFields.Add
Key:=Range("B6:B1000" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Jan 08").Sort
.SetRange Range("B6:G1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True

End Sub

Return to active cell after sort
 

I think it's your .select statement that's causing the problem:

Option Explicit
Sub SortNames()
Application.ScreenUpdating = False

with ActiveWorkbook.Worksheets("Jan 08")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B6:B1000"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
with .Sort
.SetRange .parent.Range("B6:G1000")
.Header = xlGuess 'why let excel guess if you have headers????
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
end with
End With

Application.ScreenUpdating = True

End Sub

Untested, uncompiled.

(Is this for xl2007?)

"Patrick C. Simonds" wrote:

Is it possible to edit the Sort Routine so that when the sort is completed,
the Range("B6:G1000") will be deselected and the active cell will be the
cell that was active when the Sort Routine started?

Sub SortNames()
'
' Macro1 Macro
'

'
Application.ScreenUpdating = False

ActiveWindow.SmallScroll Down:=-6
Range("B6:G1000").Select
ActiveWorkbook.Worksheets("Jan 08").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Jan 08").Sort.SortFields.Add
Key:=Range("B6:B1000" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Jan 08").Sort
.SetRange Range("B6:G1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True

End Sub

--

Dave Peterson

Return to active cell after sort
 

Thank you sir

It worked wonderfully




"Dave Peterson" wrote in message
...
I think it's your .select statement that's causing the problem:

Option Explicit
Sub SortNames()
Application.ScreenUpdating = False

with ActiveWorkbook.Worksheets("Jan 08")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B6:B1000"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
with .Sort
.SetRange .parent.Range("B6:G1000")
.Header = xlGuess 'why let excel guess if you have headers????
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
end with
End With

Application.ScreenUpdating = True

End Sub

Untested, uncompiled.

(Is this for xl2007?)

"Patrick C. Simonds" wrote:

Is it possible to edit the Sort Routine so that when the sort is
completed,
the Range("B6:G1000") will be deselected and the active cell will be
the
cell that was active when the Sort Routine started?

Sub SortNames()
'
' Macro1 Macro
'

'
Application.ScreenUpdating = False

ActiveWindow.SmallScroll Down:=-6
Range("B6:G1000").Select
ActiveWorkbook.Worksheets("Jan 08").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Jan 08").Sort.SortFields.Add
Key:=Range("B6:B1000" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Jan 08").Sort
.SetRange Range("B6:G1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True

End Sub

--

Dave Peterson