Return to active cell after sort

  • Thread starter Patrick C. Simonds
  • Start date
P

Patrick C. Simonds

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
 
D

Dave Peterson

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?)
 
P

Patrick C. Simonds

Thank you sir

It worked wonderfully




Dave Peterson said:
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?)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top