I have a macro that appends values from Sheet14 to the end of a list of data
on Sheet13, then sorts the entire group in alpha order. My first problem is
that the range (rng) isn't updated- it refers to Prescreenlist which is a
Steve Bullen auto-expanding range, but I'm guessing this code takes a
reference to that range in the beginning when the rng is set, and doesn't
update it every time a new item is added. That may or may not be related to
the second problem, which is an error with the sort command (select method
of range class failed). Using XL2003. I've noted the two rows below that are
causing problems.

1. What is the appropriate way to get the rng reference to update (use the
auto-expanded range dynamically)?
2. The select statement highlights the appropriate range on the worksheet


Private Sub cmdLoadNames_Click()

Dim rng As Range, TempRng As Range, sVal As String, res As Variant, res2 As
Variant
Set rng = ThisWorkbook.Names("Prescreenlist").RefersToRange 'the names
that have already been transferred
Set rng2 = ThisWorkbook.Names("PositionID").RefersToRange

RawID = Val(Trim(Sheet14.Range("A1").Value))
'MsgBox RawID

For I = 2 To 65000
'Check each line for a new name
RawName = Trim(Sheet14.Range("A" & CStr(I)).Value)
'Check to make sure it is a real name, not just an extra line
If Len(RawName) 4 Then
'Check to see if the name is already on the list
res = Application.Match(RawName, rng, 0)
'If no match was found
If IsError(res) Then
'add the name to the list
'next line should update rng each loop so that new name is
always appended once cell after previous name
Set TempRange = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Cells
' = Rawname '<<this overwrites each time a name is added
TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Row
Dim MyCell As Range
For Each MyCell In TempRange
MyCell.Value = RawName
Next
'add position info
'1. find which column
res2 = Application.Match(RawID, rng2, 0)
'2. have to ensure a position match before processing
If IsError(res2) Then
MsgBox "The position ID was not found", , "Position ID
mismatch"
ClearOld = False
Exit Sub
Else
'3. check to see if info is already in that cell
UseCol = ConvertCol(res2 + 1)
TempCol = UseCol
TempOld = Sheet13.Range(TempCol & CStr(TempRow)).Value
If TempOld = "" Then
Sheet13.Range(TempCol & CStr(TempRow)).Value = "A"
ElseIf TempOld = "A" Then
'do nothing
Else
'a value already exists! do nothing for now, assume that
we
'don't want to overwrite an existing code
End If
End If

End If
Else
Exit For 'exit loop b/c end of names list
End If
Next

Sheet14.Columns("A:A").Select
Selection.ClearContents
Range("A1").Select

Sheet13.Activate
Sheet13.Range("A11:Z10000").Select
Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlNo, _
'<<this is where the code breaks
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheet13.Range("A11").Select

End Sub