How to sort a worksheet programmatically

[Guest]

Excel 2003. I am trying to programmatically sort a worksheet. I used the
Macro recorder to do it and copied the code. My workbook is open and the
worsheet is open (ShNew). Yet, the following gives me an error:

ShNew.Range("A2:F" + Trim(Str(I))).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

What am I doing wrong? Or better yet, simply, "How do I perform the sort on
ShNew?"

 

[Guest]

I found my problem. In the code example given:
Selection.Sort Key1:=Range("D1")
needed to be changed to:
Selection.Sort Key1:=ShNew.Range("D1")

 

[Tom Ogilvy]

Does "I" have a numeric value greater than 0 and less than 65536?

Dim rng as Range
Set rng = ShNew.Range("A2:F" & I)

rng.Sort Key1:=shNew.Range("D1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal