| xcel 2007 records the sort operation differently than Excel 2003. In
2007, the range is specified in the SetRange method while in 2003 the
Selection object is used. Take this simple checkbook workbook.
If you record a macro to sort on check number in 2003, you get something like this
Sub Macro3()
‘
‘ Macro3 Macro
‘ Macro recorded 2/20/2011 by Dick Kusleika
‘
‘
Range("A1″).Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("B2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Cell A1 is selected, then the current region by using the Goto –
Special dialog (F5). The sort is done on the selected range. Contrast
that with a macro recorded in 2007.
Sub Macro1()
‘
‘ Macro1 Macro
‘
‘
Range("A1″).Select
Selection.CurrentRegion.Select
ActiveWorkbook.Worksheets("Sheet1″).Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1″).Sort.SortFields.Add Key:=Range("B2:B12″) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1″).Sort
.SetRange Range("A1:E12″)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End
Sub
In this version, the range A1:E12 is hard-coded. When a new row is added, the 2003 code works the same and the 2007 code fails.
Like many readers of DDoE, I don’t accept recorded code. I only use
it to discover objects, properties, and methods necessary. This is a
special case. This workbook is used to instruct people who are not just
new to Excel, but new to computers in general. Having them open the
VBE, much less edit code, is out of the question.
I considered using Lists (Tables in 2007), but they act so
differently in the two versions that I ruled it out. Ultimately I want
to demonstrate recording a macro that sorts on check number and another
macro that resorts on date. Any ideas on how I can accomplish this
without editing the code?
| 
