Convert Recorded Macro Selections to Current Selection Excel 2007

Here's a recorded macro. I'm having no luck changing the selection(s) to "active selection". I think the search terms required to get an answer are simply too generic.

Bottom line: I want to sort the active selection without being prompted to extend it. Sort A-->Z. I will run the macro from a shape on the worksheet, so I just need a standard sub. Thanks soooo much!!

Sub Macro3()
'
' Macro3 Macro
'

'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("G5:G11")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

LVL 22
Anne TroyEast Coast ManagerAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Do you want to sort on the first field of your selection? Try this:

Sub Macro3()
'
' Macro3 Macro
'

'
    activesheet.Sort.SortFields.Clear
activesheet.Sort.SortFields.Add Key:=selection.cells(1,1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With activesheet.Sort
        .SetRange selection
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
                                  

Open in new window

0
 
Rory ArchibaldConnect With a Mentor Commented:
I would think:
Sub Macro3()
'
' Macro3 Macro
'

'
    With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=Selection.Cells(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        End With
        .SetRange Selection
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window


should do it
0
 
nutschCommented:
photo finish.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Rory ArchibaldCommented:
:)
0
 
Anne TroyEast Coast ManagerAuthor Commented:
LOL. I'll check it out tomorrow guys! Thanks so much. :D
0
 
Anne TroyEast Coast ManagerAuthor Commented:
Beauteous! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.