Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make active cell a variable, return there later | Excel Programming | |||
Return to active cell | Excel Discussion (Misc queries) | |||
Return to active cell after macro completes | Excel Programming | |||
I need to sort an active sheet using the col of the active cell | Excel Programming | |||
In macros, an instruction that will return the position of the Active Cell | Excel Programming |